vlookup on a cell with a formula

[Guest]

Hi,

I have added the values(text) of 2 columns together by either eg. =a1&""&a2
or =concatenate(a1,a2). I want to use the result of this as the lookup_value
in a vlookup formula but get a #N/A error. Is it possible to use vlookup in
such an instance? If so what can I do to make the formula work?


Thanks

 

[Bob Phillips]

Minos,

Try using

--(A1&A2)

as the lookup value

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[vezerid]

Hi,

I have added the values(text) of 2 columns together by either eg.
=a1&""&a2
or =concatenate(a1,a2). I want to use the result of this as the
lookup_value
in a vlookup formula but get a #N/A error. Is it possible to use
vlookup in
such an instance? If so what can I do to make the formula work?


Thanks

Minos,
the concatenated values appear in *single* first column of your table?
Or are they broken in two columns? If this is the case, then you need
to use the combination INDEX(..., MATCH(...)) as an array formula (to
be entered with Shift+Ctrl+Enter)

=INDEX(K1:K10, MATCH(a1&b1, I1:I10&J1:J10,0))

In this example I am assuming your table lies in cells I1:K10, with
columns I:J containing the values that form the key and K:K containing
the values you want retrieved.

HTH
Kostis Vezerides

 

[Bruno Campanini]

Hi,

I have added the values(text) of 2 columns together by either eg.
=a1&""&a2
or =concatenate(a1,a2). I want to use the result of this as the
lookup_value
in a vlookup formula but get a #N/A error. Is it possible to use vlookup
in
such an instance? If so what can I do to make the formula work?


Thanks

Don't add/concatenate anything.
Simply use A1&A2 as the lookup value.

Bruno