CODEWITHSUNDEEP
excelreferenceexcel-formulamatchcell
Kamil
Kamil

Reputation: 1

Excel 2016 'MATCH','ISERROR','IF' formulas with result depending from the match; refering to a cell nearby the result

As in title, I'm trying to compare two lists and when a formula hits the match I want it to type the text, but from the cell right next to the found matching cell.

For example: A list of 5343 positions B1:B5343 has corresponding reference numbers in A column (A1:A5343). F1 has a certain number. G1 ought to show the reference number, not the match itself.

I've tried to work with this formula:

=IF(ISERROR(MATCH(F1;$B$1:$B$5343;0));"";A1)

Where A1 should be the reference cell. Not fluent in Excel enough to know

is there a way to refer to a formula result?
is it possible to refer to a cell next to the cell of interest (or few away)? ex.(B-1)54=A54

Not sure how can I clarify the problem more than that :) Help me out please!

Eager to read Your answers! Cheers!

Upvotes: 0

Views: 442

Answers (2)

Som Shekhar
Som Shekhar

Reputation: 138

This is a typical case for Vlookup

Upvotes: 1

chrisy brown
chrisy brown

Reputation: 39

i would probably go for a index match on this, so INDEX(C:C,MATCH(F1,B:B,0),1) the index part will go get the cell next to it you want, just replace C:C with the column its in

If its not a match use iferror, rather than iserror as well, Its better practice.

SO : IFERROR( INDEX(C:C,MATCH(F1,B:B,0),1) ,"NO MATCH)

Heres a tutorial

Upvotes: 0

Related Questions