Reference a cell value in a vlookup function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
Is it possible to reference a cell value as the lookup_value in a vlookup
Click to expand...
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 said:
Is it possible to reference a cell value as the lookup_value in a vlookup
Click to expand...
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)
 
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
 
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(C3>10, "6", IF(C3>8, "5", IF(C3>6, "4", IF(C3>4, "3", IF(C3>1, "2", "1"))))) and get 3
C4=9 I use the formula =IF(C4>6, "6", IF(C4>5, "5", IF(C4>4, "4", IF(C4>2, "3", IF(C4>1, "2", "1"))))) and get 6
C5=2 I use the formula =IF(C5>9, "6", IF(C5>6, "5", IF(C5>4, "4", IF(C5>2, "3", IF(C5>0, "2", "1"))))) and get 2
C6=3 I use the formula =IF(C6>6, "6", IF(C6>4, "5", IF(C6>3, "4", IF(C6>2, "3", IF(C6>0, "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.
 
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)
 
Back
Top