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
• 27,160 points
82 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
• 50,080 points

Related Questions In Others

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
• 50,080 points
116 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
• 50,080 points
105 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
• 50,080 points
133 views
0 votes
1 answer

Excel Formula multiple Index Match and Average the result

If the conditions are separate and unrelated, ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 50,080 points
388 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
• 50,080 points
54 views
0 votes
1 answer
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
203 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
357 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
• 50,080 points
112 views
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
• 50,080 points
119 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