Vlook using 2 cells to form the criteria

[ppenguin33]

I work for a city, and what I am attempting to do is use two cells ( that collectively ) make an address, For example in cell one would be the house number 712 and in cell 2 would be the street address 5th st nw. These would correspond to two other cells in a data base to lookup and return some serial numbers. I want it to look up the address and then fill into the form that I have pre made. I've already looked up and know how to fill in a form with the VLOOKUP, however I'm having troubles with the fact that I'm using two separate cells.

 

[Michael M]

Have you considered entering a helper column and joining the 2 cells, eg..

=A1&B1

then use that helper column in the VLOOKUP ?


If there is a space required in the helper column, use this instead

=A1&" "&B1

 

[ppenguin33]

Have you considered entering a helper column and joining the 2 cells, eg..

=A1&B1

then use that helper column in the VLOOKUP ?


If there is a space required in the helper column, use this instead

=A1&" "&B1

I've been thinking that i might just have to do that, but the problem is that the database that i'm creating will be home to thousands of entries, each with up to 10 cells with it, i wanted to avoid adding more if possible, but that ok,
So, what i'm doing is look up address in the database tab would i just combine the two columns and then add them after that?

 

[Michael M]

If your street number was in A1 and your street name was in B1, you simply use the formulas suppiled above in a seperate column....it could be waaay over to the right !!
then use that new column reference in your VLOOKUP
If you get stuck use the HTML Maker in my tag and post a SMALL sample, maybe a couple of lines, and we'll have a look.
But it's pretty simple

 

[ppenguin33]

So Ive done that and it still doesn't work.
=VLOOKUP(W2,Database!1:1048576,16,FALSE)
That my lookup and I've numbered my entries so it would look it by number so, 1234 2nd Avenue is #20, and it'll pull #20 but not that address. and I did combine them also.

 

[Michael M]

Your VLOOKUP doesn't have any column references in the table array
for example

=VLOOKUP(W2,Database!A1:Z1048576,16,FALSE)

AND
Do you really need to check a million rows ??
Only use the table range required, not the entire sheet

 

[ppenguin33]

Your VLOOKUP doesn't have any column references in the table array
for example

=VLOOKUP(W2,Database!A1:Z1048576,16,FALSE)

AND
Do you really need to check a million rows ??
Only use the table range required, not the entire sheet

Well eventually my table will contain thousands of entries eventually. If I'm putting this into a table will it update automatically?

 

[ppenguin33]

So I tried adding in the column letters ( a & Z ) that didn't work at all, I also tried to name it to my table and that didn't work either

 

[ppenguin33]

ok so in the end I had to move column d ( the one with the combined addresses ) to column a, and then it all worked.

 

[Michael M]

Glad you got it sorted
I probably should have posted this earlier....
A great site for VLOOKUP learning


http://www.contextures.com/xlFunctions02.html