CODEWITHSUNDEEP
excel
Nas Chad
Nas Chad

Reputation: 13

Excel formula if a cell is equal to a specific range, copy the cell next to the matching cell

Can I get an Excel formula for if a cell is equal to a cell of a specific range, copy the cell next to the matching cell, otherwise show 0.

I.e. from the screenshot in D2, check if C2 is in the range of F2:F8 and if so, show 100 (the cell next to the corresponding F2) otherwise, show 0.

enter image description here

Upvotes: 1

Views: 225

Answers (2)

Will F
Will F

Reputation: 191

This will check if the vlookup returns an error and return 0, otherwise, it will do the vlookup normally.

=IFERROR(VLOOKUP(C2,$F$2:$G$8,2,FALSE),0)

If you want to catch only #N/A errors (and not #REF errors, for example) there is another approach. Note that this requires two vlookups and therefore runs slower.

=IF(ISNA(VLOOKUP(C2,$F$2:$G$8,2,FALSE)),0,VLOOKUP(C2,$F$2:$G$8,2,FALSE))

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

Thought I'd throw this in the mix: =IFERROR(INDEX($G:$G,MATCH($C2,$F:$F,0)),0)

VLOOKUP is great for looking to the right, but if you want to look to the left then INDEX/MATCH is the way to go (although not relevant in this instance).

Upvotes: 1

Related Questions