Excel 2007 : VLOOKUP not Working

**maryne11** · 07-02-2010, 11:18 PM

HELP!!
I have tried everything that I know (including a work around that is way too time consuming) to get the vlookup to work. I have checked the formating of the cells (as someone suggested here) and still I am not seeing the expected results. I cannot identify the issue- I believe that it has to be in the formatting due to my work around working, but again my work around is too time consuming.
I have attached a sample of the file- and am hoping that someone can help. The data is there, it just will not pull it to the sheet, it keeps bringing back N/A.

If anyone can assist - I would be very grateful!

maryne

**hardeep.kanwar** · 07-02-2010, 11:49 PM

First Convert Formula to Value Using Paste Value Command and then Convert to Number

**Paul** · 07-03-2010, 01:53 AM

Hi Maryne, welcome to the forum.

The problem is due to the fact that the values in column E on sheet CMS 2005 are text, while the values you're looking up are numeric. VLOOKUP doesn't like that very much. Rather than pasting values over top of your formulas (losing the formulas in the process), change the formula in E3 on sheet CMS 2005 to:

=IF(D3="",CONCATENATE(C3,".03"),IF(D3=26,CONCATENATE(C3,".02"),IF(D3="TC",CONCATENATE(C3,".01"),IF(D3="RR",CONCATENATE(C3,".04"),IF(D3="UE",CONCATENATE(C3,".05"),IF(D3="NU",CONCATENATE(C3,".06"),IF(D3=53,CONCATENATE(C3,".53"),CONCATENATE(C3,".03"))))))))+0

Fill that down to the bottom of your data in column E. Format those cells as Numeric with 2 decimals. Your VLOOKUPS should then work fine.

**Paul** · 07-03-2010, 02:02 AM

As a possible alternative, just change the formula in E3 on sheet CMS 2005 (and filled down) to:

=C3+LOOKUP(D3,{0,26,53,"NU","RR","TC","UE"},{0.03,0.02,0.53,0.06,0.04,0.01,0.05})

In the LOOKUP function, the first array are the search values and are usually in ascending order (0<9<A<Z) to work properly. The second array has the return values based on which search value was matched.