Indexing Multiple Tables

**Haywire79** · 08-11-2010, 07:46 PM

I've been doing quite a bit more in Excel lately and have even taken a class or two that was supposed to be "Advanced". Most everything I've learned in these classes are things I've already self taught, and for my latest endeavor, very little help.

I've simplified my example for security and brevity. Actual worksheet is 38 columns by 7000+ rows.

Here's the situation:

Current customer product inventories for monthly recurring services are listed in the A4:C58 table. What I am trying to accomplish is to write a formula with parameters of Product, State, and Term/Qty based on the product pricing tables to automate the process of pricing out the new promo price for column D. Also, since there are products that are not eligible for promotion, have those return the current price listed.

I've been trying to use INDEX in an IF or VLOOKUP but can't quite map it out to work.

Any help would be appreciated.

**JBeaucaire** · 08-12-2010, 12:02 AM

Perhaps something like this.

The colored sections are NAMED RANGES I've created to make the formula simpler to construct. K5:R18 is named ProductA, etc.

The a named range for States and another for Periods along the left and top of one of the the first table.

The formula D5 is a standard INDEX/MATCH, but the INDEX() portion uses INDIRECT to index the correct named range based on the value in column A.

=INDEX(INDIRECT("Product" & A5), MATCH($B5, States, 0), MATCH($G$2, Periods, 0))

Copied down, that formula finds the correct cell in the indexed range and returns the value. The #REF answers are for the tables yet to be created and named.

**Haywire79** · 08-12-2010, 12:19 AM

That's awesome. Thanks for the help. One issue on the #REF values is that there are thousands of products that I don't have a database to pull the prices from. So for these products I would need to refer back to the original price. Any thoughts?

**JBeaucaire** · 08-12-2010, 12:32 AM

Try this in D5, then copy down:

=IF(ISERROR(INDEX(INDIRECT("Product" & A5), 1, 1)), C5, INDEX(INDIRECT("Product" & A5), MATCH($B5, States, 0), MATCH($G$2, Periods, 0)))

**DonkeyOte** · 08-12-2010, 03:20 AM

Given the return is a number you can use:

=LOOKUP(9.99E+307,CHOOSE({1,2},C5,existingformula))

Given the tables are to all intents and purposes identical there's no real need for Volatile INDIRECT (and/or Named Ranges) as I see it.

Simply reference all tables in the INDEX range and adjust the row_index accordingly (ie Match Product and offset by Match of State), ie:

=LOOKUP(9.99E+307,CHOOSE({1,2},C5,INDEX($K$4:$R$83,MATCH($A5,$J$4:$J$83,0)+MATCH($B5,$J$5:$J$18,0),MATCH($G$2,$K$4:$R$4,0))))

Above assumes Product Name and States are never the same (ie no Product AZ)

**Haywire79** · 08-12-2010, 05:53 PM

Originally Posted by DonkeyOte

Given the return is a number you can use:

=LOOKUP(9.99E+307,CHOOSE({1,2},C5,existingformula))

Given the tables are to all intents and purposes identical there's no real need for Volatile INDIRECT (and/or Named Ranges) as I see it.

Simply reference all tables in the INDEX range and adjust the row_index accordingly (ie Match Product and offset by Match of State), ie:

=LOOKUP(9.99E+307,CHOOSE({1,2},C5,INDEX($K$4:$R$83,MATCH($A5,$J$4:$J$83,0)+MATCH($B5,$J$5:$J$18,0),MATCH($G$2,$K$4:$R$4,0))))

Above assumes Product Name and States are never the same (ie no Product AZ)

Wow, that works out great. I was able to put that in fairly easily. I did run into one issue that there is the possibility that when dealing with the products, there are instances where one item can have two product names. This happens when a product is an addition to an existing product whereby both are listed but only one carries the dollar value and the new formula would return a new value for both products thereby double billing.

I solved that by adding an =IF(C5=0,0,[remaining formula] so it would precede and always give a zero dollar amount for products that start at zero.

For my learning, I can work backwards through the INDEX and beyond, but I can't seem to track the =LOOKUP(9.99E+307,CHOOSE({1,2},C5, part of the string.

Anything that would help me better understand this part would be appreciated.

**DonkeyOte** · 08-13-2010, 02:12 AM

From a prior post:

Originally Posted by D.O

LOOKUP assumes all values in the lookup_vector are sorted in ascending order (irrespective of reality)

LOOKUP returns last value <= criteria in the lookup_vector (or associated value from result_vector if specified)

LOOKUP ignores all values in the lookup_vector that are not of the same data type as the criteria - this (importantly) includes error values
ie error values in the lookup_vector won't cause the LOOKUP to fail.

So in this context:

i) the criteria is a VERY big number

ii) the lookup_vector itself consists of two values c/o of CHOOSE: the original price and the result of the INDEX

ii) the lookup_vector items are ordered in terms of least preference (this is important)

It follows then that the LOOKUP will return the last number found in the lookup_vector which will either be the promotion price (Index succeeds) or where no such price exists (Index error) the original price.