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 82 views

## 1 answer to this question.

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.

• 50,080 points

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

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

## Excel Formula multiple Index Match and Average the result

If the conditions are separate and unrelated, ...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

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE