How do I make vlookup retrieve text

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I have a table1 on one sheet such as this
1 Text1 0.10 0.30 0.50
2 Text2 0.20 0.40 0.60
etc

I have a seperate sheet in the same spreadsheet that uses vlookup to obtain
values from this table1. It retrieves the numbers correctly but I can't get
it to return the Text1. It tells me the value isn't valid. I am just
trying to put the text into a cell while doing nothing to it so I am not
adding it or any other math operation. How can I get the text????
 
Hi

the VLOOKUP function requires that the "lookup_value" (ie the first
parameter) is the left most column of the table_array (the 2nd parameter) so
you can't do this
if
D4 = 0.10
and your table below is in A1:D2
this formula will not work
=VLOOKUP(D4,D1:D2,1,0)

so for a solution we need to know what you're "looking" up .....

Cheers
JulieD
 
If you use vlookup(1,a1:x21,2,0) you will get text1
or
vlookup("text1",b1:x21,1,0)
or use match/index combination
 
<<"this formula will not work
=VLOOKUP(D4,D1:D2,1,0)">>

Why not ???

First, Jim should post the formula that he's using.

However, I would guess that the problem might be that the "Text" doesn't
match *exactly* with each other, (lookup value with lookup column).
Could be trailing or leading spaces.

Key in a test Text value into the lookup column, then copy it to the lookup
value cell, and see what happens.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi

the VLOOKUP function requires that the "lookup_value" (ie the first
parameter) is the left most column of the table_array (the 2nd parameter) so
you can't do this
if
D4 = 0.10
and your table below is in A1:D2
this formula will not work
=VLOOKUP(D4,D1:D2,1,0)

so for a solution we need to know what you're "looking" up .....

Cheers
JulieD
 
Hi RD

with my example what i said was in D4 was 0.10
so the VLOOKUP formula i posted equated to
VLOOKUP(0.10,{Text1,0.10;Text2,0.20},1,0)
which,AFAIK won't work ....
however, i assumed, and maybe incorrectly that the OP's example had row
numbers 1, 2 .... Don took another approach and assumed these were the
lookup values
in which case with 1 in D4 and the following formula
=VLOOKUP(1,D1:D2,2,0)
Text 1 will be returned.

Cheers
JulieD
 
Here is what I am trying to do. I have the following table on one sheet.

Index Land Use % Imper A B C D
1 Natural 0 0.20 0.25 0.30 0.35
2 LDR 1.0 DU/A or less 10 0.27 0.32 0.36 0.41
3 LDR 2.0 DU/A or less 20 0.34 0.38 0.42 0.46
4 LDR 2.9 DU/A or less 25 0.38 0.41 0.45 0.49


From another sheet I am using the following formula to look up the numbers
under the A,B,C, D columns.
=B9*VLOOKUP(F$2,Sheet1!A$3:H$14,3)
It looks up the value under the A column matching the Index row and returns
the right value..

I am also trying to get the text under Land Use using the following formula
=B9*VLOOKUP(F$2,Sheet1!A$3:H$14,2)
all I get is #VALUE! as a result. Why will it return numbers but not text?
 
Hi

two things

1) you're using an approximate match in your VLOOKUP statement which means
that VLOOKUP will return the result of the first closest match rather than
looking for an exact match (for an exact match use the fourth parameter of 0
or FALSE)
2) you're multipling the result of the "text" VLOOKUP by B9 ... remove the
B9* and it should work.

Cheers
JulieD
 
You are right, works fine now. I was copying a cell that worked and was
going to
change the column reference but I forgot about the fact it was multiplying
the adjacent cell.
Thanks
 
glad its solved and thanks for the feedback
Jim said:
You are right, works fine now. I was copying a cell that worked and was
going to
change the column reference but I forgot about the fact it was multiplying
the adjacent cell.
Thanks
Click to expand...
 
Back
Top