Screwed up Vlookup

[bobhc]

Good day all


I have a spread sheet with a couple of Vlookups in it. One is a straight forward Vlookup returning the values from a single row.

The other is a bit more complicated and returns all the multi row data that is attributed to the person via the drop down name box. The problem is it does not do what it should……..but I must point out this is code I received help with some time back and it worked then but I have managed (without trying) to screw it up.

What is happening with the Iferror multi lookup is it is skipping the first row of data selecting the remaining rows PLUS the first row from the next name. So you end up with the first row missed from the named array and the first row of the next named array. My finger tips are red raw and blooded from the splinters from head scratching (solid oak), I would appreciate help but I would like help in a pointer as to where the mistake is.


Please find worksheet upload:


https://dl.dropbox.com/u/34893656/Multi_Vlookup.xlsx

 

[Hui]

Bobhc

In J16:

=IFERROR(INDEX($B$3:$E$41, SMALL(IF($A$3:$A$41=$J$15,ROW($A$3:$A$41)-2), COLUMNS($J$16:J16)),ROWS($J$16:J16)), "")

Ctrl+Shift+Enter

Then Copy across

Then Copy J16:M16 Down


or simply use this: https://www.dropbox.com/s/6bc3oqhi2opgadw/Multi_Vlookup-1-Hui.xlsx

 

[bobhc]

Good day Hui

When you compare the two codes you can tell just how far of I was, my thanks for your help. Have put your formula in and all returns for all named arrays are correct, thank you for your time and the upload

 

[Hui]

Bobhc

Can you please email me on click on Excel Ninja to the left, Email at bottom of page


Thanx


Hui...