VLOOKUP doesnt work

Hiya,

I downloaded a daily sales excel format from internet, on which I altered few things, like adding a new column (price) on inventory data sheet.The problem is in the sales data sheet, when I enter a product number (and press tab), the product name or description auto fills or appears, but this does not happen the same with price. when I enter the product code I want the price to appear or auto fill the cell same like product name or description. I tried several times with VLOOKUP and it does not work. I have attached the file, where if you check on sales data sheet, row no. 105 col H, I tried using VLOOKUP and it doesnt work. Please do help.

Many Thanks
Lazy.

Try

=VLOOKUP('Sales Data'!$F$105,Inventory!$B$9:$D$87,3,FALSE)


Price is the 3rd item in the range: column B=1, C=2, D=3)

THANK YOU VERY MUCH INDEED. IT ABSOLUTELY WORKED. I was actually counting the columns from outside the selected area. once again thanks very much indeed.

There is one other thing which I would like to do. How do we make the price column in sales data sheet 'AED 0.00' if we do not enter any product details (I mean if we leave it black). right now when i put the formula (vlookup) on price column and leave the product column blank then it shows '#N/A'

Hi lazy_tuna you can use the IFERROR formula as follows:

=IFERROR(VLOOKUP('Sales Data'!$F$105,Inventory!$B$9:$D$87,3,FALSE),0)

*Duplicate post

Try

=IFERROR(VLOOKUP('Sales Data'!$F$105,Inventory!$B$9:$D$87,3,FALSE),0)

I set the error condition to zero (0) to avoid other errors if the price is used in other calculations.

The following will set it to blank BUT you may need to change other formulae

=IFERROR(VLOOKUP('Sales Data'!$F$105,Inventory!$B$9:$D$87,3,FALSE)," ")

BUT you will need to add a test in Column J to avoid a value error

e.g.

=IFERROR('Sales Data'!$H106-'Sales Data'!$I106,"")

spot on. thank you very much indeed. the problem is solved.

Could you please mark thread as solved ("Thread Tools" at top of first post).