match/look up????

[cke]

I have the following data on one worksheet (worksheet 1)
A B C
1 000 1735 $4563.55
2 009 5100 $84.23
3 007 2159 $649.35
4 007 5420 $2.95
The other worksheet (2) looks like this:
A B C
1 000 1735
2 007 5420
3 007 2159
4 009 5100
On worksheet 2, I want to put in C1, that if A1 and B1, match that same as the A&B in worksheet 1, put the $ amount in C.
Example….Since A2 & B2 in worksheet 2, match exactly to A4 & B4 in worksheet 1, then C2 in worksheet 2 is $2.95

 

[wigi]

What are the contents of column A on both sheets?

 

[cke]

sorry, formatting went off....A is actually the 3 digit "Cost Center", while the 1-4 represent the row number. B should be the 4 digit "dept", then C the $ amount

 

[Matty]

Hi,

The following solution assumes that the combination of values in Columns A and B in Sheet1 represents a unique reference.

In C1 of Sheet2, try:

=INDEX(Sheet1!C$1:C$100,MATCH(1,IF(Sheet1!A$1:A$100=A1,IF(Sheet1!B$1:B$100=B1,1)),0))

Which is an array formula and therefore needs committing with CTRL+SHIFT+ENTER, and can then be copied down.

Matty

 

[cke]

Great! Thank you!

 

[Matty]

Great! Thank you!

No problem. Glad to help.

Matty