If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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.
|
#2
|
|||
|
|||
Reference a cell value in a vlookup function
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. |
#3
|
|||
|
|||
Reference a cell value in a vlookup function
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. |
#4
|
|||
|
|||
Reference a cell value in a vlookup function
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. |
#5
|
|||
|
|||
Reference a cell value in a vlookup function
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 |
#6
|
|||
|
|||
Reference a cell value in a vlookup 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 |
#7
|
|||
|
|||
Reference a cell value in a vlookup 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. |
#8
|
|||
|
|||
Reference a cell value in a vlookup function
Thank you, that has been driving me crazy!
|
Thread Tools | |
Display Modes | |
|
|