Finding matching values from different columns

[hefe_espada]

Hello!

I am having trouble with what seems to be a easy problem to fix. This is the gist of what I am trying to accomplish: I have four columns: A, B, C, and D. A and B are related (meaning the data in A1 should correspond to B1) and C and D are related in the same way. Column B is blank. In this example, Column A and Column C are titled "Property Address" and Column B and Column D are titled "Year Built" I want to find out if any value in Column A matches any value in Column C, and if so, display the corresponding value from column D in the blank column B. So if The value for A1 = "111 N Main St", B1 is blank, C26 = "111 N Main St", I want to display the value from D26 in cell B1. Does that make sense? And if there is no match, I would like column B to display "No Match".

I cannot just sort the data because (in my example) column A has about 6,000 records and column C has about 30,000 records.

[Moo the Dog]

How about something like this in B1:

=IF(ISNA(VLOOKUP(A1,$C$1:$D$30000,2,0)),"No Match",VLOOKUP(A1,$C$1:$D$30000,2,0))

- Moo


* Newer versions of Excel [2007+] (yours is 2003) make it a smaller formula:
=IFERROR(VLOOKUP(A1,C:D,2,0),"No Match")

[hefe_espada]

Thank you! I used what you gave me above and simplified it to this: =VLOOKUP(A1,C:D,2,0) and it worked! It just returned "N/A" for the ones with no match but that is fine. Thank you!!!

[FDibbins]

Hi and welcome to the forum

If you are using 2003, it may be better to restrict your range to what Moo suggested, rather than using entire columns - otherwise it may slow your file down if you have a lot of data/formula.

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)