VLookup - Match- INDEX with multiple values across sheets

Hi All,

I have two sheets that I am working on ='Table Of Values'! and ='Report Template'!.

In cell AE14 of the sheet ='Report Template'! I have the name of the city. I would like to match and list the related data of the column "Location Rates" that are in T9:T192 in ='Table Of Values'! based on the city name. I also need the name of the Location Rates' Owner Property which is listed in A9:A192. I would like to collect the listed Location Rate and the Location Rate Owner Property values in ='Report Template'!. Cities are listed in C9:C192 in ='Table Of Values'!. How can I do this?

Please help.

I would suggest if you can provide a sample of your worksheet and you will be help more.

regards,
Rev12

Hi Rev12,

Thank you so much for your time and offer to help!

I prepared the attached example. Will you be able to help? I am really desperate.

you've Excel 2016 so try PowerQuery (Get&Transform)

In B7

=IFERROR(INDEX(Tabel1[Code],SMALL(IF(Tabel1[City]='Report Sample'!$C$2,ROW(Tabel1[Code])-ROW($A$4)+1,""),ROWS($1:1))),"")

in C7

=IFERROR(INDEX(Tabel1[Code],SMALL(IF(Tabel1[City]='Report Sample'!$C$2,ROW(Tabel1[Code])-ROW($A$4)+1,""),ROWS($1:1))),"")

Enter BOTH ...



...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

THEN copy down

Originally Posted by tylops

Hi Rev12,

Thank you so much for your time and offer to help!

I prepared the attached example. Will you be able to help? I am really desperate.

You can make it easy in a PivotTable I make a sample from you worksheet and a formula if you wish to as below;

Under Code 'Report Sample' Sheet
=IFERROR(INDEX(Tabel1[Code],SMALL(IF(Tabel1[City]=$C$2,ROW(Tabel1[Code])-ROW($A$4)+1,""),ROWS($1:1))),"")

Under Location Rate 'Report Sample' Sheet
=IFERROR(INDEX(Tabel1[Location Rate],SMALL(IF(Tabel1[City]=$C$2,ROW(Tabel1[Location Rate])-ROW($A$4)+1,""),ROWS($1:1))),"")

you need to press Ctrl + Shift + Enter (because it's an array fuction)
and copy down to corresponding fieldnames

hope it helps,
Rev12

Thank you all!! You are life savers...

You are welcome

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.