Search for value in column Excel

0
votes

I want to match a value in a cell, with an entire column. In other words, if col1 contains the current record in col2, it should be a match. Even though it is not in the same row. The results should be as follows:

Col1       Col2       Result
-------    -------    -------
A          B          match
B          F          not match
C          C          match
D          A          match

I have looked into VLOOKUP and LOOKUP but it does not seem to be able to provide me with what I need

Here is what I have tried so far:

=IFERROR(VLOOKUP(P:P,C:C,15,FALSE), "NO MATCH")

in col 17 which resulted to:

col15  col16 col17
A      D     NO MATCH
B      B     NO MATCH
C      A     NO MATCH
D      F     NO MATCH
4
excelexcel-formula
Why is it not working? Can you include the formula you've tried? – L42
Is C:C column the lookup column? If so, the P:P reference should point to every cell you want to compare, as P1, P2 and so on – Rubens Farias

4 Answers

1
votes

This may also help you. Try following formula.

=IFERROR(IF(MATCH(B2,$A$2:$A$5,0)>0,"Match"),"Not Match")
0
votes

You can go with:

=IF(ISNA(INDEX(A:A;MATCH(B1;A:A;0);1));"NOT MATCH";"MATCH")

Where:

  • A:A: the range where you want to search for values; in your example, the Col1; notice that range appears twice in the formula
  • B1: the value you're looking for; in your example, every values from Col2

Try to remove the IF and ISNA functions to see how the INDEX behaves when it doesn't find a match.

This approach is superior to the VLOOKUP alternative, as it doesn't requires the lookup column to be sorted.

0
votes

Why not you are using match formula? Use this formula....

=IF(ISNUMBER(MATCH(B2,$A$2:$A$5,0)),"Match","Not match")

Screenshot enter image description here

0
votes

Your formula:

=IFERROR(VLOOKUP(P:P,C:C,15,FALSE), "NO MATCH")

didn't work because your Vlookup results to an error.
Main reason is you put 15 in column_index but you actually don't have 15th column since you only select 1 column which is C:C for table_array.

Another is you lookup_value should be a single cell so instead of P:P, it should be P1 for example.

To make it work, you will need to change it to:

=IFERROR(VLOOKUP(P1,C:C,1,FALSE), "NO MATCH")

It will result to the value found or "NO MATCH" for non existing value.
To get your desired result, this formula should work.

=IF(ISERROR(VLOOKUP(P1,C:C,1,FALSE)),"not match","match")