Complicated Lookup...

[tiffsauder]

Hello,

I have a document with several sheets on it that contain tables of pricing for individual products. (When I say tables I do not mean tables that were made with the tables feature. I simply created them by formatting the cells within the table. There is no sorting options or anything included. Without the formatting of fill colors, and font features they would simply be cells with formulas.)

20%	All Purpose Scraper
Qty Breaks	250	500	1000	2500
R	Net	R	Net	R	Net	R	Net
11000	$0.80	$0.48	$0.76	$0.46	$0.71	$0.43	$0.67	$0.40

<colgroup><col style="text-align: center; "><col span="8" style="text-align: center; "></colgroup><tbody>
</tbody>

I copy and pasted one of the tables above.

I am new to the forum so I am not sure how to display things in the best way. Tips for that would be wonderful!

The quantities are the 250, 500, 1000, 2500 listed under the All Purpose Scraper which is the item, the item number is listed under the "Qty Breaks". The item number will need to be my lookup value. The prices below each quantity apply to the customer based on how many pieces they purchase. Under the quantity is letter "R" this is a code that is equal to a percentage. Next to it is the "Net" price. Under each of those is the price that applies. All the prices are figured based on formulas. The two cells above the "R" and "Net" are merged and contain the quantity.

What I need to do is return return the above values in a table like this...

Item Number	Quantity 1	Coded Price	Net Price
11000	250	$.80	$.48

<tbody>
</tbody>

I need a formula that will return the values in red...

Can anyone help?

 

[MikeGeno]

If I understand correctly you are trying to create a Vlookup to pull the values from the corresponding columns based on the Quantity?

You could try a nested VLOOKUP for the column reference. Create a small table in the sheet with the item as follows the states the column position of the data required based on the Qty selected

Qty</SPAN>	Coded Column Ref</SPAN>	Net Column Ref</SPAN>
250</SPAN>	2</SPAN>	3</SPAN>
500</SPAN>	4</SPAN>	5</SPAN>
1000</SPAN>	6</SPAN>	7</SPAN>
2500</SPAN>	8</SPAN>	9</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

(I will just pretend I named this range QTY for the formula) Also assume that the pricing data is sitting in a sheet called data

To Get Coded Price based on the volume the formula in Column B would be
=vlookup($A1,Data!$A$1:$A$9,vlookup($B$1,QTY,2,false),false)

To Get Net Price based on the volume the formula in Column B would be
=vlookup($A1,Data!$A$1:$A$9,vlookup($B$1,QTY,3,false),false)

The first part of the formulas looks for the Item number, the second vlookup tells the formula which price to return based on the quantity entered.

Hope this is clear or helps. This works assuming all of your pricing data is in one tab, if each item has it's own sheet you would have to do come up with a different solution which I am not sure how to do. I am also unclear wheter or not the QTY in your last table will be keyed by you or will need to be returned by the formula.

 

[tiffsauder]

I really need to avoid making the table with the column references. I have a lot of products and it would be more time consuming than just typing everything in by hand.

I know nothing about HLOOKUP but would that help at all...is there any other options I have without creating anything new and simply working from the tables I have?

Thanks for you help!

tiffsauder

 

[MikeGeno]

You couldn't use HLOOKUP in this instance you would first have to unmerge the cells with the QTY and type the QTy in twice, and when you did the HLOOKUP it would always find the leftmost value and look down so it would always return the coded price.

Sorry that was my only suggestion you'll need an expert for a more advanced solution.

 

[tiffsauder]

Thanks for your help! Hopefully I can combine a few things. Your suggestion did get me thinking though. I never considered combining the lookup functions!