Excel is giving me trouble. I'm currently using a vlookup, but since the lookup column contains strings and integers, I think it would be best to swap it out for an index match function.

What I want the formula to check for: I want the formula to see if column O contains a numeric value. If so, then check up the value in cell A2 and locate the appropriate value for sales on sheet 2 by looking up the value. The result of the formula is either a sales value or an empty value.

Below is the current VLOOKUP I'm using.

```=IFERROR(IF(ISNUMBER(O2),VLOOKUP(A2,'Sheet2'!\$C\$2:\$E\$10,3,FALSE)),0)
```

Can someone kindly suggest how I might do the same using INDEX MATCH?

Nov 16, 2022 in Others 446 views

Try:

```=IFERROR(IF(ISNUMBER(O2),VLOOKUP(A2,Sheet2!\$C\$2:\$E\$10,3,FALSE),""),"")

=IFERROR(IF(ISNUMBER(O2),INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(A2,Sheet2!\$C\$2:\$C\$10,0)),""),"")
```

where the first "" means that O2 is not a number, while the second "" means there is no match (error).

If you meant that the 'resulting' column E contains text values that you don't want to show, try:

```=IFERROR(IF(ISNUMBER(VLOOKUP(A2,Sheet2!\$C\$2:\$E\$10,3,FALSE)),VLOOKUP(A2,Sheet2!\$C\$2:\$E\$10,3,FALSE),""),"")

=IFERROR(IF(ISNUMBER(INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(A2,Sheet2!\$C\$2:\$C\$10,0))),INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(A2,Sheet2!\$C\$2:\$C\$10,0)),""),"")
```

The INDEX/MATCH is safer because it will still work correctly even if you insert or delete a column between C and E in Sheet2.

• 63,420 points

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

Excel create an Index on the first sheet with links to subsequent sheets

This seems to be a potential duplicate ...READ MORE

INDEX MATCH formula in Excel returning incorrect results in some cells and correct results in others

Step through the formula that produces the ...READ MORE

Index and Match multiple matches

You can try the following (formula 1) ...READ MORE

Excel worksheet multi-criteria function (like Index-Match array) to fetch last value of an item, looking up by item name and date

Use: =SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12)) It will return the value at the ...READ MORE