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 in Others by Kithuzzz
• 20,660 points
22 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 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Excel - IF Formula with a FIND

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

answered Sep 27 in Others by narikkadan
• 37,660 points
56 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 in Others by narikkadan
• 37,660 points
50 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 in Others by narikkadan
• 37,660 points
73 views
0 votes
1 answer

Excel formula that combines MATCH, INDEX and OFFSET

Try this: =INDEX((OFFSET(SheetA!F:F,0,SheetA!A1)),MATCH(SheetB!C4, ...READ MORE

answered Nov 7 in Others by narikkadan
• 37,660 points
53 views
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 in Others by Edureka
• 13,640 points
142 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 in Others by Edureka
• 13,640 points
240 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
109 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
71 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 in Others by narikkadan
• 37,660 points
59 views
0 votes
1 answer

Excel If field with rounding

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

answered Sep 27 in Others by narikkadan
• 37,660 points
52 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