Log In
Register
Home
Active Member
March 29, 2019
Offline
Hi all,
I am wanting to do a lookup to return multiple results. I would usually use a vlookup for this, I am looking up to data that has more than 1 value, and I would like to list each of the results vertically. I have spent a fair bit of time searching online, but none of the formulas I have found work when I do it, I'm not sure what I'm doing wrong. In the attached sheet, for postode 4405 for example, there are 5 or 6 suburbs within that postcode. Using a vlookup would only return 1 result, I'm wanting to return a list of all results. Can anyone help?
Thanks,
James
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi James
Attached are 2 methods that you can consider.
One uses an array formula and the other uses a Pivot Table.
Please note I have deleted some of your data to keep the file size within the maximum limit allowed to be uploaded.
Hope this helps.
Sunny
Answers Post
Active Member
March 29, 2019
Offline
Hi SunnyKow, that's what I'm after. That's a huge help - thanks!
Active Member
March 29, 2019
Offline
Hi SunnyKow,
The file you sent worked, however when I click in the formula bar of a cell and press enter, it no longer works, I'm having trouble transferring the formula into the file I'm trying to work with. When I open the file you sent, it has { at the start, and } at the end of the formula, when I press enter in the formula bar it clears these brackets. If I manually put them back in, the formula doesn't work. Any ideas?
VIP
Trusted Members
December 7, 2016
Offline
Hello,
The formula Sunny provided is an array formula. https://www.myonlinetraininghu.....ay-formula
Just press the control and shift keyes and then press enter.
/Anders
1 Guest(s)
