Excel INDEX MATCH Checking Multiple Columns

arraymicrosoft excelvlookup

The problem I'm essentially trying to solve is a VLOOKUP that is checking Columns A:E for a value, and returning the value held in Column F should it be found in any of these.

With VLOOKUP not being up to the task I have looked into the INDEX-MATCH syntax, but I am struggling to get my head around how to complete this for an array of values, as opposed to a single column. I've built an example data set below to try and explain this:

A------B------C------D------E------F

1------2------3------4------5------Apple

12-----13--------------------------Banana

14---------------------------------Carrot

Should the cell being checked contain 1,2,3,4 or 5, the result of the formula should be Apple. If it is 12 or 13, it should return Banana and finally if it contains 14, it should return Carrot.

The second half to this comes from the fact that the cell being referenced isn't a single value, but a full table itself. As such, this search will be completed a large number of times according to different values.

So to demonstrate, there is another table elsewhere (as below) that has these values in. I am attempting to have the system identify which row, and therefore which of the "Apple, Banana, Carrot" values to associate with each column. The table would look as below

H——I————

1——(Apple)—-

2——(Apple)—-

12—–(Banana)-

etc.—————–

The values in brackets are where the formula is calculating these values.

Best Answer

You have a number of different cases. Let's consider one case:

Somewhere in columns A through E there is one and only cell containing 13, return the contents of the cell in column F in the same row.

We will use a "helper" column. In G1 enter:

=COUNTIF(A1:E1,13)

and copy down. This allows us to identify the row:

enter image description here
Now we can use MATCH()/INDEX():

Pick a cell and enter:

=INDEX(F:F,MATCH(1,G:G,0))

enter image description here

If the "rules" change and there could be more than one 13 in a row or several rows containing 13, we would modify the helper column.

EDIT#1:

Based on your update, the first step would be to pull the hard-coded 13 out of the formulas in the "helper" column and put it in its own cell, (say H1). Then you can run different cases simply by changing a single cell.

If you have a large number of cases in a table, you could create a macro to setup each case (update H1) and record the results.

Related Question