Lookup a value in multiple columns in another spreadsheet

[mariap]

Hi,

I need to look up a number that may or may not be in one of three columns in a different spreadsheet - if an exact match is found I need to return a value in the same row in another column. For example

A Linked Spreadsheet

Col A Col B Col C Col D
1111 3333 4444 Bob Brown
999 8888 7777 Jane Smith

Another spreadsheet

Col A Col B
1111 Here I would like a formula that will look up the value in Col A
(1111) and check if it appears in either Col A, B or C in the Linked Spreadsheet if it does then give me the value in Col D "Bob Brown" if not "No Match"

My Linked spreadsheet is very large up to 40000 rows it would need to check over the three columns so I'm looking for something that is efficient

Any ideas appreciated

Thanks,
maria

 

[vane0326]

See if this works.


=INDEX(Sheet2!D2:D10,LOOKUP(10^307,CHOOSE({1,2,3},MATCH(A2,Sheet2!A2:A10,0),MATCH(A2,Sheet2!B2:B10,0),MATCH(A2,Sheet2!C2:C10,0))))


Hope it helps!

 

[mariap]

Hi,
Great thanks for the response but I'm a bit lost

can we break it down so I get my reference right

=INDEX(Sheet2!D2:D10 - I'm not sure what this is the cell reference for?

LOOKUP(10^307, - what does 10 ^ 307 mean?

CHOOSE({1,2,3},MATCH(A2,Sheet2!A2:A10,0),MATCH(A2,Sheet2!B2:B10,0),MATCH(A2,Sheet2!C2:C10,0)))) - do I leave as choose 1,2,3 ?and is this saying match the value in A2 to column A2:A10, then try column B then C.

If I can't find a match will it say 0 ?

thanks,
maria

 

[mariap]

Lastly,

How do I catch the the #NA - with an Index ? If nothing matches I'd like to say "No Match" rather than #NA else give me the match value.

Is it something like IF(ISNA(=INDEX(Sheet2!D2:D10,LOOKUP(10^307,CHOOSE({1,2,3},MATCH(A2,Sheet2!A2:A10,0),MATCH(A2,Sheet2!B2:B10,0),MATCH(A2,Sheet2!C2:C10,0)))) = True, INDEX(Sheet2!D2:D10,LOOKUP(10^307,CHOOSE({1,2,3},MATCH(A2,Sheet2!A2:A10,0),MATCH(A2,Sheet2!B2:B10,0),MATCH(A2,Sheet2!C2:C10,0)))))

 

[vane0326]

See if this example will help you.


=LOOKUP(REPT("z",255),CHOOSE({1,2},"No Match",INDEX(D2:D10,LOOKUP(10^307,CHOOSE({1,2,3},MATCH(F2,A2:A10,0),MATCH(F2,B2:B10,0),MATCH(F2,C2:C10,0))))))

Book1
	A	B	C	D	E	F	G	H
1
2	1111	3333	4444	Bob		3333	Bob
3	999	8888	7777	Jane
4
5
6
Sheet1

 

[mariap]

Thanks worked a treat