Excel - IF combined with Index Match

0 votes

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 by Kithuzzz
• 38,010 points
468 views

1 answer to this question.

0 votes

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.

answered Nov 17, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

IF function in combination with an Round function Excel

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

answered Sep 25, 2022 in Others by narikkadan
• 63,420 points
2,428 views
0 votes
1 answer

Excel If field with rounding

Here's your formula. Assumes your cell containing ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,420 points
530 views
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,420 points
520 views
0 votes
1 answer

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

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

answered Oct 22, 2022 in Others by narikkadan
• 63,420 points
850 views
0 votes
1 answer

Excel - VLOOKUP vs. INDEX/MATCH - Which is better?

Since it is much more adaptable and ...READ MORE

answered Dec 23, 2022 in Others by narikkadan
• 63,420 points
247 views
0 votes
1 answer
0 votes
1 answer

Index and Match multiple matches

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

answered Feb 10, 2023 in Others by narikkadan
• 63,420 points
282 views
0 votes
1 answer

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

answered Apr 6, 2023 in Others by narikkadan
• 63,420 points
413 views
0 votes
1 answer

Excel Count if with index match

Try this: Countif(Index(rangeoflookupvalues,,match(Columnvaluetobe ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 63,420 points
1,444 views
0 votes
1 answer

Excel Nested IF INDEX MATCH Function

Use COUNTIF and an array if all ...READ MORE

answered Apr 1, 2023 in Others by narikkadan
• 63,420 points
611 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP