Thread: Using VLOOKUP to get the from a certain table (invoicing and pricing)

Ok so here I have a worksheet with 2 sheets.

1st sheet named Sheet1 is my invoicing sheet.

2nd sheet is named pricelist which contains the prices for the items to be sold at each customer class (A, B, C etc)
Now what i cannot figure out is how to use VLOOKUP to get values from pricelist BASED on the customer class and show them on column O (price).

Also note that i have 3 item descriptions. ex. Ballpen - Panda - black. Any help would be appreciated.

This formula goes in cell O3 on Sheet1

You will need to extend the ranges to suit your Price List.

Vlookups are great when you have a single value to look up. When you have more than that, you need to think again. In this case, I have used SUMPRODUCT to solve the first part of the problem. The second part is that I don't know which price list to use, ABC. I therefore used the OFFSET and MATCH functions to determine which column of prices. The Match will return 1, 2, or 3. This can be used as an offset from Column C on the price list to give the correct price.

=SUMPRODUCT(('price list'!$A$2:$A$40=Sheet1!K3)*('price list'!$B$2:$B$40=Sheet1!L3)*('price list'!$C$2:$C$40=Sheet1!M3)*(OFFSET('price list'!$C$2:$C$40,0,MATCH(Sheet1!C3,'price list'!$D$1:$F$1))))

Any questions, feel free to ask.

Cheers, Rob.

Originally Posted by dilipandey

Hi kdylim,

See the attached file and look in Cell o2 (orange colored). Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi so I tried your solution and it works for the first item only. So i tried to add things to the formula (extending the search range from a1:18 to a1:a200 and adding the 3rd item description to the mix)

I still have some problems that i cannot find the solution to. here is the file, any help would be very much appreciated