Looking up data by row and column (Arrays, Lookups, Index, Match??

[Guest]

Hello:

I'm pretty comfortable with VLOOKUPS but having a difficult time
understanding MATCH and INDEX. I'm trying to fill in column D on Database
Table with column C from SAP Table where Columns A and B from each table
matches.

I was thinking an array would work, but I don't have much experience with
this either.

For example: A3 & B3 of SAP Table = A2 & B2 of Database Table

SAP Table
A B C
1 PersNo. End Date Vacation Hours
2 0007635A 07/28/2007 40.00
3 0011203A 06/30/2007 40.00
4 9400171A 07/28/2007 40.00
5 9400180A 07/28/2007 40.00



Database Table
A B C D
1 SAP Number End Date Vacation SAP Vacation
2 0011203A 6/30/07 40 Result of formula here.
3 9400180A 4/14/07 40

Thanks for any suggestions.

 

[Guest]

One way

Assume source "SAP table" is in sheet: X

In the other sheet ("Database Table"),
Put in D2, array-enter the formula by pressing Ctrl+Shift+Enter:
=INDEX(X!C$2:C$100,MATCH(1,(X!A$2:A$100=A2)*(X!B$2:B$100=B2),0))
Copy D2 down. Adjust the ranges to suit.

 

[Guest]

It WORKED!!! Thank you so much.

One way

Assume source "SAP table" is in sheet: X

In the other sheet ("Database Table"),
Put in D2, array-enter the formula by pressing Ctrl+Shift+Enter:
=INDEX(X!C$2:C$100,MATCH(1,(X!A$2:A$100=A2)*(X!B$2:B$100=B2),0))
Copy D2 down. Adjust the ranges to suit.

 

[Max]

welcome, glad it helped.