Forum Discussion
how to get a function to recognize a cell with numbers and text?
I am trying to create a formula which will match mailing addresses. I have two seperate lists of mailing addresses, which are in a random order but contain the same addresses. I need a formula which will match the addresses and then pull information from spread sheet B onto spreadsheet A.
It seems i'll be able to use VLOOKUP for this but my problem is that the function is only looking for the number or the street name, but not both.
How do I format the cell so it will try to match both parts of the address ?
Hi Maxine,
you can create a column with unique concatenated values of street number and street name in the source table, then use either VLOOKUP or (even better) INDEX/MATCH to pull data, which would concatenate the lookup value in the same way as in the source table
If you had street name in A1, street number in B1 in both 'Source' and 'Dest' sheets, a suburb in C1 of the 'Source' sheet, and would like to pull the subub into C1 of the 'Dest' sheet, you can concatenate street number and name in D1 of the source sheet using formula =A1&B1, and put the the following formula in C1 of the destination sheet:
=INDEX(Source!C:C,MATCH(A1&B1,Source!D:D,0))
Regards
Yury
