two cell appended vlookup

[ammartino44]

Hello. How could I create a vlookup using text from two cells joined together. For example, A1 has Jim and B1 has 55, and I want to use A155 as the lookup value. Is it better to just concatenate first or can I create a vlookup and concatenate within the formula?

[GC Excel]

You'll get the same results with both methods.

Either:

=VLOOKUP(CONCATENATE(A1,B1),$E$1:$G$100,3,0)

or

=VLOOKUP(C1,$E$1:$G$100,3,0)

where
C1=CONCATENATE(A1,B1)

[FDibbins]

This would also work...
=VLOOKUP(A1&B1,$E$1:$G$100,3,0)

Note that column E (the 1st column of your range) would need to contain that combined value, somewhere

[ammartino44]

I just realized that I need to have a "-" between the two cells. The two cells are numbers and I think when I do this =B3&"-"&C3 it returns a text value because it's not finding it in the lookup. How can I make it keep the values as numbers?

[FDibbins]

I just realized that I need to have a "-" between the two cells. The two cells are numbers and I think when I do this =B3&"-"&C3 it returns a text value because it's not finding it in the lookup. How can I make it keep the values as numbers?

When you join 2 values (using & or Concat) they default to text. If you are searching in a column with contents like 123-456, that is also not a number, it is text

[Jdevil]

If there's a - in between, it can't be a number, since 136-12 is not a number. You need to format it the same way it is in your vlookup table. Could you provide an example perhaps?