Reference a cell value in a vlookup function

Is it possible to reference a cell value as the lookup_value in a vlookup function? I tried using the INDIRECT function, but got a #REF error.

To use the cell value as the lookup_value, simply use the cell address
(e.g., A3). You can do this by pointing. No need for INDIRECT.

--

Vasant



"Michelle" wrote in message
...
Is it possible to reference a cell value as the lookup_value in a vlookup
function? I tried using the INDIRECT function, but got a #REF error.

Yes.

I agree that's not much of an answer, but your post wasn't much of a
question!

What are you trying to achieve? What values? What formula did you try? What
result did you expect? What did you get instead?
Please post again!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Michelle" wrote in message
...
Is it possible to reference a cell value as the lookup_value in a vlookup
function? I tried using the INDIRECT function, but got a #REF error.

Hi
normally no need to use INDIRECT in this case. Just simply enter
something like
=VLOOKUP(A1,range,2,0)

--
Regards
Frank Kabel
Frankfurt, Germany


Michelle wrote:
Is it possible to reference a cell value as the lookup_value in a
vlookup function? I tried using the INDIRECT function, but got a
#REF error.

I have two columns of data: in column A there are four digit numbers and in column B there are text strings. I come up with the four digit number in cell H11 and am trying to return the text string associated with it. This is the formula I originally used: =VLOOKUP(H11, Segment, 2, FALSE) where Segment = A17:B1312
that returns the error message #N/A

I tried =VLOOKUP(INDIRECT($H$11), Segment, 2, FALSE)
that returns #REF

I dont know if it matters, but the value in H11 is from a CONCATENATE function

Hi
the first version should work if H11 has an EXACT match in column A. So
I assume that they're not identical. e.g. text values and numbers or
spaces, etc.
you may post some example data (plain text - no attachment please) or
you may try to compare H11 manually. e.g. if you think that H11 should
match with A20 try the formula
=H11=A20
this should return TRUE



--
Regards
Frank Kabel
Frankfurt, Germany


Michelle wrote:
I have two columns of data: in column A there are four digit numbers
and in column B there are text strings. I come up with the four
digit number in cell H11 and am trying to return the text string
associated with it. This is the formula I originally used:
=VLOOKUP(H11, Segment, 2, FALSE) where Segment = A17:B1312 that
returns the error message #N/A

I tried =VLOOKUP(INDIRECT($H$11), Segment, 2, FALSE)
that returns #REF

I dont know if it matters, but the value in H11 is from a CONCATENATE
function

Hi Michelle
do you type '3623' with apostrophes or without. If you use the latter
one try the following formula
=VLOOKUP(--H11, Segment, 2, FALSE)

--
Regards
Frank Kabel
Frankfurt, Germany


Michelle wrote:
Yes, there is an exact match in the range. This may get kind of
confusing:

I am trying to compute the scores for a personality test we gave to
all our employees. come up with four numbers that if I score by hand
I plot on a chart, but I am using nested if then statements to return
another four numbers. For instance:
C3=6 I use the formula =IF(C310, "6", IF(C38, "5", IF(C36, "4",
IF(C34, "3", IF(C31, "2", "1"))))) and get 3
C4=9 I use the formula =IF(C46, "6", IF(C45, "5", IF(C44, "4",
IF(C42, "3", IF(C41, "2", "1"))))) and get 6
C5=2 I use the formula =IF(C59, "6", IF(C56, "5", IF(C54, "4",
IF(C52, "3", IF(C50, "2", "1"))))) and get 2
C6=3 I use the formula =IF(C66, "6", IF(C64, "5", IF(C63, "4",
IF(C62, "3", IF(C60, "2", "1"))))) and get 3

then, in cell H11 I have =CONCATENATE(C11,D11,E11,F11) to give me
3623 and that is what I am trying to look up in my range with
=VLOOKUP(H11, Segment, 2, FALSE) it is supposed to return the
personlaity trait of Promoter. When I manually type "3623" into the
formula, I get Promoter.

Thank you, that has been driving me crazy!