CODEWITHSUNDEEP
excelif-statementindexingexcel-formulamatch
Kelley Hamrick
Kelley Hamrick

Reputation: 197

INDEX, MATCH two criteria, with one of those criteria conditional... where does the IF go?

I keep getting #N/A as an error for the following function:

=INDEX(G3:G50,MATCH(1,(IF(B3:B50=O2,B3:$B50=O2,C3:C50=O2)*($D3:$D50=A2)),0))

I have two lists of names - some are in the B column and others are in the C column. If the name is in C, it will appear in B as 0. I'd like to INDEX, MATCH with the appropriate name - so, the formula should reference C if B=0, otherwise it should use B.

O2 is the name I have in a different sheet that should match either B or C.

Upvotes: 0

Views: 1540

Answers (1)

pnuts
pnuts

Reputation: 59475

I still not sure I understand the requirement fully but "the formula should reference C if B=0, otherwise it should use B" seems fairly easy to deal with. If looking, with MATCH and the requirement that any match be exact, for a value in B that does not exist (ie failing to find such a value) an error will be returned.

IFERROR is a function that allows a default when its first term gives rise to an error. So we can use that along the lines of "IFERROR(try this, but if that bombs out then this instead)". So, assuming you want to find the content of "the row in Column G" (ie a cell) that is in the same row as whichever of B or C matches O2 in your other sheet, something like this may suit: 

 =IFERROR(INDEX(G:G,MATCH(Sheet2!O2,B:B,0)),INDEX(G:G,MATCH(Sheet2!O2,C:C,0)))

Equally, may not suit, but might still help towards an answer for you. I have generalised by considering whole columns but recommend you limit your ranges to suit (ie as in your OP).

As you may gather, I have not figured out quite where A2 and ColumnD fit in.

Upvotes: 1

Related Questions