How to search multiple columns using INDEX and MATCH?

Question

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?

Try this =INDEX('PNO Lookup'!$A:$J,MATCH(Summary!D25,'PNO Lookup'!$G:$J,0)) — Praveen DA
– Praveen DA, Commented Feb 9, 2018 at 2:31
@david_10001 you have not given the column index number. The formula works like this Index(lookup range, row number, column number) you are missing the column number — Praveen DA
– Praveen DA, Commented Feb 9, 2018 at 2:38
@PraveenRKaruppannan column number is optional. If the range is only one column or row it is not needed. — Scott Craner
– Scott Craner, Commented Feb 9, 2018 at 2:40

Scott Craner · Accepted Answer · 2018-02-09 02:37:24Z

2

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)))

answered Feb 9, 2018 at 2:37

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user4039065 Over a year ago

Good for column G or column J; personally I found the match column description confusing. e.g. 'two different columns' vs, ''PNO Lookup'!$G:$J' vs. 'from column G to column J' vs. 'both column G and J'.

Scott Craner Over a year ago

yes I assumed the first and the last. Either way yours is shorter.

Scott Craner Over a year ago

don't know about that, been on the road a lot lately. I don't see that stopping anytime soon.

user4039065 · Accepted Answer · 2018-02-09 02:40:19Z

2

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)))))

answered Feb 9, 2018 at 2:40

user4039065

Collectives™ on Stack Overflow

How to search multiple columns using INDEX and MATCH?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related