Issue with LOOKUP and Advice on crrect formulae

**pranabdeb** · 04-12-2012, 10:21 AM

Dear All

Please see the attached file name TESTTest.xls, Here values in Summary sheet see column AL i am using lookup to get Description for the cost head from Sheet1. Now my problem is in some of the cells see AL26 the result is Zero, i dont know why this is coming zero here, i tried all tricks but could not get rid of this.

Secondly, while i am using LOOKUP here i want to have a similar formulae to be able to match to the item in Column AK, in Summary with Table A:D (sheet1) and return values from column F.

Any response to this would be appreciated

Best regards

Pranab Deb

**canapone** · 04-12-2012, 10:28 AM

Hi,

maybe in AL10 and down

Please Login or Register  to view this content.

or as Excel 2010 user

Please Login or Register  to view this content.

Regards

**darknation144** · 04-12-2012, 10:38 AM

I really can't figure out why your lookup() messes up when vlookup() works perfectly.

**canapone** · 04-13-2012, 08:14 AM

Hi Darknation144,

maybe (not sure) LOOKUP function needs to work with vectors in ascending order to give a correct feedback.

Regards

**pranabdeb** · 04-15-2012, 05:11 AM

Dear Canapone,

Test 2.xls see here still AP69, and AP70 is blank where as 6.1 abd 1.3 are valid gigures in rge Sheet1 List. Can you also advice if in Sheet1 a formula exist though which the data between Column A,B, C and D be checked with those in AK in summary to update values in AL.

Best regards

Pranab Deb

**canapone** · 04-15-2012, 06:45 AM

Hi,

a possibility

=IF(AK9="","",IF(ISERROR(VLOOKUP(AK9,Sheet1!$E$3:$F$151,2,0)),VLOOKUP(SUBSTITUTE(AK9,",","."),Sheet1!$E$3:$F$151,2,0),VLOOKUP(AK9,Sheet1!$E$3:$F$151,2,0)))

Regards

**pranabdeb** · 04-15-2012, 07:29 AM

Dear Canapone

Thank you for your continuos help!

In this excel sheet Test 2.xls can you get the formulae right to select from the Table in SHEET1 a match for the item in AK column of Summary and return a value in column AL.

Best regards

Pranab

**canapone** · 04-15-2012, 08:33 AM

Hi,

see the example attached.

in AL10 and down

Please Login or Register  to view this content.

There are for sure better solutions.

Regards from Firenze

**pranabdeb** · 04-18-2012, 03:44 AM

Dear Canapone

Thank you very much for this. It works perfectly now, So you are from Florence, thats nice, Last August I was in Italy and visited Rome/Milan/Venice etc.

Thank you once again and regards from Warm Dubai