INDEX and MATCH - Multiple lookup?

**tomlancaster** · 02-11-2010, 06:04 PM

Hi, not sure how to explain this but here goes...

I would like to bring in some data from another workbook if 2 conditions are met:

1. The Date
2. The User ID

Is it possible to do this with INDEX and MATCH? I tried doing it like this if it helps explain a bit better:

Sheet 1. Column C:
=INDEX(Sheet2!C:C,MATCH(Sheet1!A2&Sheet1!B2,Sheet2!A:A&Sheet2!B:B,0))

Sheet 1
A: Date
B: User ID
C: Comments (Where above formula is)

Sheet 2
A: Date
B: User ID
C: Comments

Kind regards
Tom

**martindwilson** · 02-11-2010, 06:41 PM

=INDEX(Sheet2!C1:C100,MATCH(A1&B1,INDEX(Sheet2!A1:A100&Sheet2!B1:B100,0),0))

**teylyn** · 02-11-2010, 06:48 PM

Hi tom,

the easieset would probably be to introduce a new column C in your Sheet2 with a formula like

=A1&B1

copied down.

Then on Sheet1 you can use

=if(isna(match(A1&B1,Sheet2!$C$1:$C$100,0)),"",vlookup(A1&B1,Sheet2!$C$1:$D$100,2,false))

hth