CODEWITHSUNDEEP
excelmatch
JoeP
JoeP

Reputation: 876

Excel INDEX MATCH when value may be in one of 3 columns

I am using INDEX/MATCH to find values in a single column and return the value from a different column using:

=INDEX(Sheet1!$A:$A,MATCH(A2,Sheet1!$B:$B,0))

This will search to see if A2 matches a value in Sheet1 column B, and if so will return the value from that row in column A.

However, the value that is being searched might actually be in column B, C or D and I would like to be able to search for these values in the same query.

I was hoping that the following would work, but it does not:

=INDEX(Sheet1!$A:$A,MATCH(A2,Sheet1!$B:$D,0))

Is there a way to do this simply without IF statements? I have a version working with 3 columns using an IF statement but it seems that there should be a much easier way around it.

Upvotes: 0

Views: 2704

Answers (3)

rwilson
rwilson

Reputation: 2145

If you are just returning a number value from column A, you can use:

=MAX(IF(A2=Sheet1!$B$2:$D$10,Sheet1!$A$2:$A$10))

Enter with CTRL+SHIFT+ENTER.

If instead you are returning a text value (or a number value) from column A, you can use:

=INDEX(Sheet1!$A$2:$A$10,SMALL(IF(A2=Sheet1!$B$2:$D$10,ROW(Sheet1!$A$2:$A$10)-ROW($A$2)+1),1))

Also needs to be entered with CTRL + SHIFT + ENTER.

You can change the ranges as necessary.

Upvotes: 1

KFichter
KFichter

Reputation: 783

Honestly a little convoluted, but something like:

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

Accomplishes the same thing, although it's an array formula so you will have to enter with CTRL+SHIFT+ENTER (also works if the value shows up more than once because it's really just checking for >0)

Upvotes: 1

XOR LX
XOR LX

Reputation: 7742

You can use SUMPRODUCT, though this requires that the search value occur no more than once within the range:

=IF(COUNTIF(Sheet1!$B1:$D10,A2),INDEX(Sheet1!$A:$A,SUMPRODUCT((Sheet1!$B1:$D10=A2)*ROW(Sheet1!$B1:$D10))),"No Match")

If the search value can occur more than once within the range, the solution would depend upon which of those should take precedence when determining the return.

Note that the use of an array-processing function (SUMPRODUCT) means that you would be ill-advised to use entire column references. Unlike e.g. COUNTIF(S)/SUMIF(S), such formulas calculate over all cells passed to them, whether technically beyond the last-used cells in those ranges or not.

A suitably low, though sufficient, upper bound should be chosen for the end row reference. Alternatively, you can create dynamic ranges for your data, such that they automatically adjust as that data expands/contracts, thus offering optimal efficiency.

Regards

Upvotes: 1

Related Questions