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
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
Hi SunnyKow, that's what I'm after. That's a huge help - thanks!
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?
Hello,
The formula Sunny provided is an array formula. https://www.myonlinetraininghub.com/excel-array-formula
Just press the control and shift keyes and then press enter.
/Anders
