Vlookup/ Lookup Value

[Kris_inv2013]

Hi there,
I wonder if someone could help me with this one..
I have A1 column with the following values:
E2
E3
E5
E7
E10
(E is a radiator type, the numbers after reflect the surface in sq feet.)
In column D I have removed "E" so that I only have the numbers shown in the cells (MID or REPLACE do the job very well for me.)

Now in Column E I would like to run a VLOOKUP function to look up a value from column J in a table associated to the surface. The surfce area is the first column in the table and is in ascending order.

Unfortunatelly the VLOOKUP function is only working if the "lookup value" in D1 is a number manally input. It does not take the value returned by the REPLACE/MID functions.

I was gona do it all in one but it is not working for some reason.
=VLOOKUP(MID(A2,2,LEN(A2)),J6:R42,4,FALSE)


Any help would be much apprecated.

Thanks,
Kris

A	B	C	D	E		G	H	I	J
E2			=MID(A2,2,LEN(A2))	=VLOOKUP(D2,J6:R42,4,FALSE)		2	3	47	1
E3			3	1		3	5	98	1
E5			5	2		5	3	49	2
E7			7	2		7	8	17	2
E10			10	4		10	9	36	4

<TBODY>
</TBODY>

 

[Firefly2012]

Hi

You need to ensure you are comparing like with like which means you need a numeric value from your mid rather than a textual one. Try:

=VLOOKUP(MID(A2,2,LEN(A2))+0,J6:R42,4,FALSE)

The +0 coerces the MID return to a number

 

[Kris_inv2013]

wonderful thanks! Working fine wit both, REPLACE and MID.

Thanks again,
Kris