Vlookup

**Zan03** · 02-22-2010, 12:58 PM

I'm sure i'm doing something wrong, but i can't seem to figure out what it is...

I have a list of 300 building addresses in my spreadsheet. On one spreadsheet, they are shown by their Building code, which is a shortened version of the building address (ex. If building address is 132 Canada Road the Building Code would be 132 Cana.

In another spreadsheet i have the full building address. I need to convert the building code into the actual address.

I've tried the formula below, but a n/a message keeps popping up.

I've made sure to sort the building addresses, Any idea what i'm doing wrong?

=VLOOKUP(LEFT(A2,6),Sheet3!$A$1:$B$45,2,FALSE)

**NBVC** · 02-22-2010, 01:01 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Zan03** · 02-22-2010, 01:13 PM

Thanks, please see attached as reference.

**NBVC** · 02-22-2010, 01:15 PM

tRY:

=INDEX(Sheet2!$B$1:$B$10,MATCH(A2&"*",Sheet2!$A$1:$A$10,0))

copied down.

**Zan03** · 02-22-2010, 01:28 PM

Thanks, the formula seems to gather the information, but when i plugged it in to my actual spreadsheet with 300 addresses, the formula does not always bring up the correct building address. Any suggestions?

Thanks for your time!

**NBVC** · 02-22-2010, 01:45 PM

As long as the left side of what you are looking up matches up exactly to the lookup code, then it should bring up correctly.

Can you give example of what is not matching up?