CODEWITHSUNDEEP
excel-formula
david_10001
david_10001

Reputation: 492

How to search multiple columns using INDEX and MATCH?

I have an Excel formula that matches a cell on one spredsheet and searches through a column on another, then returns the contents of adjacent cells to that column for any matches. The problem is I need to search through two different columns for that match, and if either of the columns contains the match, return another adjacent cell. I have tried using:

=INDEX('PNO Lookup'!$A:$A,MATCH(Summary!D25,'PNO Lookup'!$G:$J,0))

which should search from column G to column J, but I get a #VALUE error. Can anyone explain how I would search both column G and J for the value in cell D25?

Upvotes: 1

Views: 1935

Answers (2)

user4039065
user4039065

Reputation:

Along the same idea as the previous answer but a little shorter.

=INDEX('PNO Lookup'!$A:$A, IFERROR(MATCH(Summary!D25, 'PNO Lookup'!$G:$G, 0),
                           IFERROR(MATCH(Summary!D25, 'PNO Lookup'!$H:$H, 0),
                           IFERROR(MATCH(Summary!D25, 'PNO Lookup'!$I$I, 0),
                           MATCH(Summary!D25, 'PNO Lookup'!$J:$J, 0)))))

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152660

use:

=INDEX('PNO Lookup'!$A:$A,MIN(IFERROR(MATCH(Summary!D25,'PNO Lookup'!$G:$G,0),1E+99),IFERROR(MATCH(Summary!D25,'PNO Lookup'!$J:$J,0),1E+99)))

Upvotes: 2

Related Questions