index / match with dropdown?

[cabbey77]

Hello,

I need to populate a summary page, from a different Worksheet range B2:BE2100.
It looks like so:

DSDSKITA/1 SMALL SCREW SYSTEM IMPL KIT
Peak Demand Target Inv Cons Inv Trunk Inv Hub Inv Inv Net Inv
ANAHEIM #REF! 0 0 0 0 -1
AUSTIN #REF! 1 0 0 0 0 -1
BIRMINGHAM #REF! 1 0 0 0 0 -1
BOSTON #REF! 2 0 0 0 0 -2
CHARLOTTE #REF! 1 0 0 0 0 -1
CHICAGO #REF! 1 0 0 0 0 -1
COLUMBUS #REF! 1 0 0 0 0 -1
CONCORD #REF! 0 0 0 0 0 0
DALLAS #REF! 0 0 0 0 0 0
DENVER #REF! 1 0 0 0 0 -1
DETROIT #REF! 1 0 0 0 0 -1
ESCONDIDO #REF! 2 0 0 0 0 -2
HOUSTON #REF! 1 0 0 0 0 -1
MEMPHIS #REF! 0 0

It needs to match both the location, which is found in column C on the lookup range and the chosen Kit # (DSDSKITA/1), which is actually a dropdown so can change at a whim and is found in column D in the lookup range. The return column for the first lookup column is 51. I have tried several different index/match scenarios and they are not working properly.

Help? And thanks

 

[SkipVought]

Hi,

"I have tried several different index/match scenarios and they are not working properly."

For instance...?

And your explanation is not very clear, as well as your example. Please use the Pre icon to bracket your table example. What value from your summary table (with no headings, BTW) are you looking up in the "different worksheet"?

 

[cabbey77]

Things Ive tried:

=INDEX('Running Sums by Kit and Hub'!B2:BD2100, MATCH(1,('Running Sums by Kit and Hub'!C3:C2100 = A4)*('Running Sums by Kit and Hub'!D32100=A$1)),0),51)
=INDEX('Running Sums by Kit and Hub'!B2:BD2100, MATCH(A3,'Running Sums by Kit and Hub'!C2:C2100,0)* Match(A1,'Running Sums by Kit and Hub'!d2:d2100,0),51)

To hopefully be more clear, the Location : Kit Number combination makes the entire row unique, I want to return the peak demand, which is in column # 51.

 

[SkipVought]

Your first formula does not make sense as the lookup value is 1 and the lookup range is unintelligible.

The second I assume that the value in A3 can uniquely be found in 'Running Sums by Kit and Hub'!C2:C2100. Don't know why you're taking the product of those 2 Match return values??? Your formula should look something like this...

[tt]
=INDEX('Running Sums by Kit and Hub'!B2:BD2100, MATCH(A3,'Running Sums by Kit and Hub'!C2:C2100,0),51)
[/tt]
Where A3 is the lookup value, C2:C2100 is the lookup range and column 51 contains the return value.

 

[cabbey77]

Well that will return a match on the location, but no match on the kit #. The location itself is not unique.

Let me see if I can be more clear.

Lookup table Columns:
C: Location
D: Kit Number
E through BA : Dates
BB through BD : Calculation columns I wish to return.

Summary Table Columns
A1: DropDown with Kit #'s so end user can choose the kit they want to see
A3:A25 : Locations
B: "Peak Demand" corresponds to column 51 of Lookup table.
etc
C through H : More columns that need to be a return from a lookup in the same fashion.
Make more sense?

 

[SkipVought]

This I do not understand at all...

"It needs to match both the location, which is found in column C on the lookup range and the chosen Kit # (DSDSKITA/1), which is actually a dropdown so can change at a whim and is found in column D in the lookup range."

Please post a cogent example of the lookup value in column A of your summary and corresponding values in columns C & D of your lookup table.

 

[cabbey77]

Summary Sheet

Lookup

 

[cabbey77]

Whoops

Link

Link

 

[SkipVought]

Please post rather than link.

Need a SPECIFIC example!

 

[cabbey77]

Summary
[pre]
DSDSKITA/1 SMALL SCREW SYSTEM IMPL KIT

ANAHEIM #REF! 0 0 0 0 -1
AUSTIN #N/A 1 0 0 0 0 -1
BIRMINGHAM #REF! 1 0 0 0 0 -1
[/pre]

Lookup
[pre]
ANAHEIM 2488KIT1 4 1 0 0.20 0.38 1 1
ANAHEIM 86MX1X12 2 1 0 0.10 0.28 1 1
ANAHEIM 86PSKIT1 6 1 0 0.29 0.44 1 1
ANAHEIM 86PSKIT2 2 1 00.10 0.28 1 1 0
ANAHEIM 86PSKITA 8 1 0 0.39 0.48 1 1
AUSTIN 1526KIT4 18.5 4 3 0.90 1.14 3 3
AUSTIN 2488KIT1 2 1 0 0.10 0.28 1 1
AUSTIN 3500KITA/1 2 1 0 0.10 0.28 1 1
[/pre]

 

[SkipVought]

I See 5 Anaheim Rows in your lookup table.

How does that map into the summary table?

 

[cabbey77]

There are many more Anaheim rows.

The summary table should pull from the column indicated in the formula from the Anaheim row where the 2nd column (Kit #) that matches the selection in the A1 dropdown list. When the kit # in the Drop Down List is changed, the data should change.

So, summarily, Match the Location to the summary table selected location, Match the Kit# to the summary Drop Down list and give me column (#) from that row.

 

[SkipVought]

Are you returning ONLY numbers?

 

[SkipVought]

Also I saw no kit number DSDSKITA/1 in your look up data.

I asked to see look up data that corresponded to your summary example.

Looks to me as if a sumproduct could be used if you have one row per selection criteria.