asked on

If statement to find matching values in columns in Excel

Excel - I need to look at a list of IDs find then in another column and then place an 'X' in another column if found.
Example

Look at column AT if you find a match in column C put a X in column AL

Column C Column AL Column AT
123 X 123
123 X 345
456 234
789 567
987 321
654
321 x
321 x
321 x

irudyk

Try the following formula in column AL

IF(ISNA(VLOOKUP(C1,AT$1:AT$5,1,FALSE)),"","X")

Change C1 to the correct row number (e.g. C2 or whatever the row number you are starting at)
Change AT$1:AT$5 to be the range you want to look for values in (e.g. if the lookup range starts at row 10 and goes to row 20 the formula would be AT$10:AT$20)

ssblue

ASKER

I get an error about to many arguments

=IF(ISNA(VLOOKUP(C2,AT$2:AT$913),1,FALSE)),"","X")

ASKER CERTIFIED SOLUTION

irudyk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ssblue

ASKER

=IF(ISNA(VLOOKUP(C2,AT$2:AT$913,1,FALSE),"","X")

Still getting the same error?????

irudyk

Sorry, I had a typo in my last response which I've since corrected. You need to add another closing bracket after the FALSE

ssblue

ASKER

Got it! Thanks!!

Subodh Tiwari (Neeraj)

Another variation you may try is this...

=IF(ISNUMBER(MATCH(C2,AT:AT,0)),"X","")

Open in new window

OR simply this...

=IF(COUNTIF(AT:AT,C2),"X","")

Open in new window

Using VLookup for a single column table_array doesn't make much sense. All you need is to confirm whether a number exists in another column or not so in that case either you may use MATCH or COUNTIF function to confirm this.

ssblue

ASKER

Thanks for comment and explanation.

Subodh Tiwari (Neeraj)

You're welcome!