How to identify a column to pull from given multiple criteria?

[sschwant]

I have a table that will be linked to from another worksheet. It will have 64 columns. I need to pull the data points from each line item into an equation on this other worksheet. For example if the unique combination of criteria 1 AND criteria 2 AND criteria 3 = column 5, then the formula will pull from that column.
I'll have data validation "DV" drop downs for each criteria on the other sheet. Based on these 3 selected values, I'll need logic to identify the column.
Once I have column, I'm thinking of using the DGET function with the table below as the database range to pull in the data points for the math that needs to happen next. The data points in the table will be rates which will be applied to volumes to compute dollar amounts on this other sheet. Although I'm not sure that will work. An HLOOKUP might be a better solution.
Here's the matrix for the total columns required:

country products network cost type columns
US 9 2 2 36
Canada 4 2 2 16
Europe 3 2 2 12
total columns 64

Th table starts w/ the US and has 36 columns, followed by Canada w/ 16 and so on . . .


column number 1 2 3 4
country US US US US
product A B C D
network and cost type X Std X Inc Y Std Y Inc
Line item 1
Line item 2
Line item 3

Thanks in advance for any insight on the "optimal" approach

[FDibbins]

Hi, welcome to the forum

It would be easier to offer suggestions if we could see a sample workbook showing what you are working with, but if your layout is something like I have shown below, see if this will help...

	A	B	C	D	E	F	G
1	Factor1	Factor2	Factor3	ww	xx	yy	zz
2	11	a	aa	1	2	3	4
3	11	b	bb	10	20	30	40
4	11	a	ab	100	200	300	400
5	22	b	ba	1000	2000	3000	4000
6
7	Factor1	11	10
8	Factor2	b
9	Factor3	bb
10	column	ww

C7=INDEX($D$2:$G$5,MATCH($B$7&" "&$B$8&" "&$B$9,INDEX($A$2:$A$5&" "&$B$2:$B$5&" "&$C$2:$C$5,0),0),MATCH($B$10,$D$1:$G$1,0))

[newdoverman]

This sounds like an interesting problem but it is confusing to me how your data is arranged.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[newdoverman]

This is inspired by FDibbins' idea expanded as well as I could interpret your description of your data. The values are completely made up. There are Data Validation drop downs at the bottom of the listing from which to make choices to return a value.

[sschwant]

Thanks for the great ideas. Reattaching the file newdoverman provided with the details that will clarify the setup. See sheet 4 cells E36 and 37. Where the previous sheets have the column as a DV selection, I need the logic to tell Excel which column it is based on the 3 criteria (maybe that's all in E37, or maybe as a two step, E36 then E37 process?). Thanks again in advance for helping to nail this down!

[FDibbins]

1st, US in G37 has a trailing space - remove that

Then use this to pull in the column...
=INDEX($E$1:$BP$1,MATCH(E32&" "&E33&" "&E34,INDEX($E$2:$BP$2&" "&$E$3:$BP$3&" "&$E$4:$BP$4,0),0))

To get the Value, I added Line Item 1 to E31, then used this to pull out the answer...
=INDEX($E$5:$BP$29,MATCH($E$31,$D$5:$D$29,0),MATCH(E32&" "&E33&" "&E34,INDEX($E$2:$BP$2&" "&$E$3:$BP$3&" "&$E$4:$BP$4,0),0))
Note the different index range compared to the 1st formula (although, with the 2nd formula, you dont really need to know the column number - unless YOU need to know it?)

[sschwant]

Got your first formula working!
It would be good to know the column # just for easy look up and audit of the returned value and/or to implement an HLOOKUP formula. Thanks for catching the trailing space on G37. Haven't quite figured out why you added Line Item 1 below to E31 yet ... but I'm still stepping through your second formula.

Thanks!!

[FDibbins]

Haven't quite figured out why you added Line Item 1 below to E31 yet

That was so that you can pick a row to get the data from. You already have a column, but that will give you a row, and with the 2, you can get the value from the intersection

[sschwant]

Looks like you added a value to cell E32 as well as putting "Line Item 1" in cell E31 ... can you explain?

Thanks again!

[sschwant]

I must have been offset by a row ... figured it out. Got the second formula working now ... thanks again!

[FDibbins]

Glad we got you where you wanted to be, and thanks for the feedback