Vlookup on multiple matches in multple ranges

**sflemings** · 01-20-2010, 07:55 AM

Hi,

I've information in two sections of a worksheet, columns A and B (range named "temp1") and columns D and E (range named "temp2"). I should also say that the reason I have two sections is that each section will have about 150 rows, so instead of having a huge list of 300 rows, I've tried splitting them into two sections.
A B
1 a
1 b
3 c

D E
4 d
1 e
6 f

What I want to do it show all the results that match the value 1 using a vlookup formula. Also the values in column A and D may change, i.e if value 4 in D1 changed to 1, I would expect to see "d" included in the results.

This is the original formula I got somewhere else which sorts out my vlookup on multiple matches issue.

=VLOOKUP($B$8,INDEX(tbl,SMALL(IF($B$8=INDEX(tbl,,1),ROW(tbl)-MIN(ROW(tbl))+1,""),ROW(1:1)),,1),2,FALSE)

I've updated it to search on each range seperately which works ok but it only returns values from one range, not both and visa versa. It is an array entered formula. In a nutshell, this formula checks if there is an error performing the lookup on range "temp1". If there is an error, it searches range "temp2". If no error, it searches range "temp1".

=VLOOKUP(H2,IF(ISERROR(INDEX(temp1,SMALL(IF(H2=INDEX(temp1,,1),ROW(temp1)-MIN(ROW(temp1))+1,""),ROW(1:1)),,1)),INDEX(temp2,SMALL(IF(H2=INDEX(temp2,,1),ROW(temp2)-MIN(ROW(temp2))+1,""),ROW(1:1)),,1),INDEX(temp1,SMALL(IF(H2=INDEX(temp1,,1),ROW(temp1)-MIN(ROW(temp1))+1,""),ROW(1:1)),,1)),2,FALSE)

I have a number of tables based on the values in columns A and D that will perform the lookup which should return the following results. I haven't put in all the tables. This is the result I want to get.
H I
1 a
1 b
1 e

J K
3 c
3
3

This is the result I'm currently getting when doing a lookup on value 1.
H I
1 a
1 b
1 #NUM!

Sorry if this confuses everyone! Ask for more details if needed. I've tried to give as much info as possible.

Any ideas on how to also search on the other range?
Thanks.

**zbor** · 01-20-2010, 07:58 AM

Can you upload example?

**sflemings** · 01-20-2010, 08:05 AM

That probably would help alright. File is attached.
The only values that I would be changing is in columns A and D.
I won't be changing any values in columns H, J, L or N

I've changed the formula I'm using slightly. I've put the ISERROR check around the whole VLOOKUP function instead of being inside it. I haven't attached the new file though.

=IF(ISERROR(VLOOKUP(H1,INDEX(temp1,SMALL(IF(H1=INDEX(temp1,,1),ROW(temp1)-MIN(ROW(temp1))+1,""),ROW(1:1)),,1),2,FALSE)),VLOOKUP(H1,INDEX(temp2,SMALL(IF(H1=INDEX(temp2,,1),ROW(temp2)-MIN(ROW(temp1))+1,""),ROW(1:1)),,1),2,FALSE),VLOOKUP(H1,INDEX(temp1,SMALL(IF(H1=INDEX(temp1,,1),ROW(temp1)-MIN(ROW(temp1))+1,""),ROW(1:1)),,1),2,FALSE))

**DonkeyOte** · 01-20-2010, 08:49 AM

Realistically you're looking at a hideous formula...

Using your sample file, to utilise below suggestion, you would first need to ensure you have a header row above first result such that existing row 1 becomes row 2

With the blank header row in place:

I2:
=LOOKUP(REPT("Z",255),CHOOSE({1,2,3},"",INDEX(temp1,MATCH(1,INDEX((INDEX(temp1,0,1)=H2)*ISNA(MATCH(INDEX(temp1,0,2),I$1:I1,0)),0),0),2)&"",INDEX(temp2,MATCH(1,INDEX((INDEX(temp2,0,1)=H2)*ISNA(MATCH(INDEX(temp2,0,2),I$1:I1,0)),0),0),2)&""))
copied to I4

I2:I4 can be copied to K, M, O

does that work for you ?

EDIT:
I just noticed (13:07 UK time) that when I copied across I failed to update a couple of cell references which should both have read H2 (I was testing in a different range) - above has been corrected.