Using V or H lookup

[katiapro93]

Hi,

I have a chart which is a price list. in one column I have the size of the
product, next to it is a column with the product name, and next to it is a
column with its price. I want to do a form which looks up a product and
gives me the price. Only problem is that we have alot of the same product
names and sizes. How can I have excel look it up. I thought maybe combining
the size and product columns but that would mess up my Price list.

Example:

12oz Whole Jal Price
26oz Whole Jal Price
12oz Sliced Jal Price
26oz Sliced Jal Price

Can anyone help me?

 

[Pete_UK]

You could combine the size and product in a helper column which is
some way to the right of your price list (eg column Z), so that it
won't be seen.

Then you could use an INDEX/MATCH combination to get the Price.

But anyway, what is wrong with a price list like this:

Whole Jal 12oz Price
Whole Jal 26oz Price
Sliced Jal 12oz Price
Sliced Jal 26oz Price

?

Hope this helps.

Pete

 

[katiapro93]

Thanks, this is getting me closer to what I want, I can combine the 2 columns
as a helper but then I have never used the Index/Match functions, can you go
into it a little for me. I know I can look it up under help but I figured
you would get me straight to the point.

 

[Pete_UK]

Okay, I'll have to make some assumptions about where your data is
located. Assume that the three columns of your pricelist are on a
sheet called Prices in columns A to C, and that in Z2 of that sheet
you have this formula:

=A2&B2

copied down.

Then assume that you use E2 to enter the size and F2 to enter the
product of the item you are interested in and that you want the price
returned to G2. Put this formula in G2:

=INDEX(Prices!C:C,MATCH(E2&F2,Prices!Z:Z,0))

E2, F2 and G2 could be in a different sheet.

Hope this helps.

Pete