I am having trouble with excel, I am currently using a vlookup but as the look up column consists of string and intergers I think the best approach would be replace the vlookup with an index match function.
What I want the formula to do: I want the formula to check if there is a numeric value in column O. If this is true then lookup the value in cell A2 and find the corresponding value for sales in sheet 2. The formula then either returns a sales value or a blank 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?
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.