Excel LOOKUP Formula collects data from OFF TARGET Cell.

**Andreas Leonard** · 05-10-2013, 05:32 AM

I got this error recently:

Case:
I uses two sheet in one file, data sheet (Price List) and work sheet.
At the data sheet contains a price list. the work sheet is where I want to calculate, how much I use an item and how much it cost.
I made a list as much as 159 rows.

At Work sheet, I made a formula like this:
Cell E4 is >> =IF(D4="","",LOOKUP(B4,'Price List'!B$4:B$202,'Price List'!E$4:E$202)*B4)
The starting row of this sheet (B4) is exactly the same row number with the price list sheet that contains the data I needed.
Since its a list, I copied the formula to the lower ROWs.
All the data is at its respective place.
Until E156th row, it collects data normally.
But at E157th and E158th row, it shows no calculation result (a dash [-]).

At Data Sheet
I checked the targeted cell which is at 'Price List'!E157 and 'Price List'!E158 , it still contains numbers as I entered while the 2nd next row below ('Price List'!B160) and the rest is empty, as it should be.
I try to locate where this formula went, and made a number list from 1 and so on, starting from cell B160 and found out that that formula at E158 on Work Sheet reads the cell E202 at the Price List sheet, which is empty of course.
(For information: the data in this price list is taken from another file from another computer through my network.)

Additional Information: I did sort the data from A to Z using the sort tool.

Can somebody please help me on this problem?

Thank you in advance.

Andreas Leonard

**martindwilson** · 05-10-2013, 06:57 AM

not without seeing the real thing
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Andreas Leonard** · 05-10-2013, 08:30 AM

Januari 2013.xlsx

Thank you for such quick respond Martin..
I hope I attached it the right way..
I'm quite confused with the attaching procedure.

In the attached file, I put red cells to point the problem..

Tanks again..

**martindwilson** · 05-10-2013, 09:33 AM

the list is not sorted ascending in col b of price list that includes all the "" returned by the formula
use
=IF(D4="","",INDEX('Price List'!$E$4:$E$202,MATCH(B4,'Price List'!$B$4:$B$202,0))*d4)
instead

**Andreas Leonard** · 05-10-2013, 10:01 AM

Originally Posted by martindwilson

the list is not sorted ascending in col b of price list that includes all the "" returned by the formula
use
=IF(D4="","",INDEX('Price List'!$E$4:$E$202,MATCH(B4,'Price List'!$B$4:$B$202,0))*d4)
instead

Aaaaaahhhh.... you are absolutely right...
I forgot that I added that 'Barrel' item @ last minute...
Thank you so much Martin.... That's very helpful..
Once again, Thank you very much.. I really appreciate it..