Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Minos
 
Posts: n/a
Default vlookup on a cell with a formula

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default vlookup on a cell with a formula

Minos,

Try using

--(A1&A2)

as the lookup value

--

HTH

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


"Minos" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default vlookup on a cell with a formula


Minos Wrote:
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


--
vezerid
------------------------------------------------------------------------
vezerid's Profile: http://www.excelforum.com/member.php...o&userid=28481
View this thread: http://www.excelforum.com/showthread...hreadid=486803

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bruno Campanini
 
Posts: n/a
Default vlookup on a cell with a formula

"Minos" wrote in message
...
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
need help creating formula based on cell value Brad Excel Discussion (Misc queries) 3 April 1st 05 07:51 PM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
looking for a formula Amanda Excel Worksheet Functions 5 January 5th 05 08:37 AM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM
VLookup resulting in a blank cell... KempensBoerke Excel Worksheet Functions 1 October 28th 04 09:57 PM


All times are GMT +1. The time now is 05:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"