modification on my lookup formula

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180
I am currently using this
Code:
=IF(ISNA(v(INDEX(sheet2!A:C,MATCH(B2,sheet2!A:A,0),3)))," ",v())

The formula match B2 with columnA in sheet2 and pick up the third column value in sheet2.

Now need modify it to match B2 with columnA and C2 with columnB in sheet2 then pick up the third column value in sheet2.

Meaning lookup if two criterias is matched.

Thanks.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I am currently using this
Code:
=IF(ISNA(v(INDEX(sheet2!A:C,MATCH(B2,sheet2!A:A,0),3)))," ",v())

The formula match B2 with columnA in sheet2 and pick up the third column value in sheet2.

Now need modify it to match B2 with columnA and C2 with columnB in sheet2 then pick up the third column value in sheet2.

Meaning lookup if two criterias is matched.

Thanks.

=IF(ISNA(V(INDEX(Sheet2!$C$2:$C$400,MATCH(1,IF(Sheet2!$A$2:$A$400=B2,IF(Sheet2!$B$2:$B$400=C2,1)),0)))),"",V())

which needs to be confirmed with control+shift+enter, not with enter.

Note that you can't reference whole columns like A:A in this type of formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,401
Messages
5,547,740
Members
410,810
Latest member
thepinkbird
Top