Match #VALUE Error?

0
votes

I have two worksheets. I want to search through sheet 1 to look for the id in sheet 2, find where it matches ID and the flag, and then return the price.

I have the following formula that gives me a #VALUE error:

Column B, sheet 2

=MATCH(Sheet2!A2&"B",Sheet1!A:A&Sheet1!B:B,0)

Sheet1:

ID FLAG PRICE
-- ---- -----
1  A    12
1  B    11
2  A    10
2  B    10
3  A    12
3  B    13
4  A    14
4  B    11
5  A    12
5  B    13

Sheet2:

ID B  C
-- -- --
1
2
3
4
5

Any Ideas?

1
excelmatchformula
So in sheet 2, column B I'd like price associated with it when the ID when the flag is B...and so on – Kmat
Yup, that is correct – Kmat

1 Answers

0
votes

Try: 

=INDEX(Sheet1!$C:$C,MATCH($A2,Sheet1!$A:$A,0)+1)