How to compare one cell with a range and if TRUE - copycontent from the right cell?

user_10927174 0
Viewed 59

I need to compare two columns I and L and copy matched result from M column. It is a list of 1000+ product codes (I,L) and EAN codes (M). So if cell I1 is found in range of L1:L1000 (lets say it found in L3 cell), then formula should copy the M3 cell.

Tried VLOOKUP and MATCH and some IF, but cannot figure it out how to make it work as it returns blank or REF! or N/A or errors-out completely. I'm desperate and don't know what i'm doing wrong...

=VLOOKUP(I1:I1164,L1:L1164,13,FALSE)

and with

=IF(ISNUMBER(SEARCH(I1,L1:L1000),M1," "")

Result should be in N column.

formula cell match compare excel
2 Answers

When using VLOOKUP, you need the lookup range to include both the range of values you're looking for (which MUST to be the first column) and the return values (whose column you specify as relative to the range. So in your case, you'll be looking up in L1:M1164 and using column 2 as return results (since column M is the second of L1:M1164).

Also, the value you're looking for will probably be just an item, relative to the current line. I'd thus try it that way (in N1):

=VLOOKUP(I1;$L$1:$M$1164;2;FALSE)

Wrapping it up in an IFERROR as suggested in SJR's answer might be a good idea too.

user_3694280

Try this

=iferror(index(m1:m1000,match(i1,l1:l1000,0)),"")

The match bit returns 3, the index bit then looks for the 3rd value in column M; the Iferror returns an empty string in the event of an error (i1 is not found).

user_70080440
Related