Complex Lookup Function

[seguin85]

Hi,

I was wondering if there was a way without using array to lookup values from the following table

	A	B	C	D	E	F	G	H
1		Med	Med	Med		High	High	High
2			Cust1	Other			Cust1	Other
3	New		x1	y1			a1	b1
4	New		x2	y2			a2	b2
5	New		x3	y3			a3	b3
6
7	Major		x4	y4			a4	b4
8	Major		x5	y5			a5	b5
9	Major		x6	y6			a6	b6
10
11	Mid Cycle		x7	y7			a7	b7
12	Mid Cycle		x8	y8			a8	b8
13	Mid Cycle		x9	y9			a9	b9

<tbody>
</tbody>

So the user selects a customer, if that customer is cust1 it will grab info from column C or G, if it is not it will grab from D or H.
Next they select Med or High, if Med it will grab from C or D, if High from G or H.
Finally they select New, Major or mid cycle.

Is there a formula that can be applied to grab this information. I do have controls over headers in the data table if that makes things easier to do.

Thanks!!

 

[HackSlash]

What do you mean by select? What do you mean by grab?

Where are you putting the data?

You might be looking for "HLOOKUP" but it depends on how you want to present the data in the end;

https://support.office.com/en-us/article/HLOOKUP-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f

 

[FDibbins]

1. What determine if C or H is used?
2. which of the 3 (new/Major/Mid) do you want? Or all 3?

 

[seguin85]

Search here for values, sorry I missed a vital column... Columns C and F are just used for calculations and will not return anything to the other sheet.

	A	B	C	D	E	F	G	H
1			Med	Med	Med	High	High	High
2				Cust1	Other		Cust1	Other
3	New	Box1
4	New	Box2
5	New	Box3
6
7	Major	Box1
8	Major	Box2
9	Major	Box3
10
11	Mid Cycle	Box1
12	Mid Cycle	Box2
13	Mid Cycle	Box3

<tbody>
</tbody>

And on another sheet will be formatted as follows:

	A	B	C	D	E	F	G
1	Customer	Speed	Type	Box1	Box2	Box3
2	Cust1	High	New	G3	G4	G5
3	Joes	Med	Mid Cycle	E11	E12	E13
4	Sam	High	Major	H7	H8	H9

<tbody>
</tbody>

The values in bold are the values I want returned based on columns A,B and C and matching the header in D, E and F. Column A cannot be changed, but columns B and C can be changed accordingly.

Hope this clears things up a bit

 

[FDibbins]

ADVERTISEMENT

Still does not explain how to chose either column D or column G?

 

[seguin85]

Still does not explain how to chose either column D or column G?

Column D if Med and Cust1. Column E if Med and other customer.
Column G if High and Cust1. Column H if Med and other customer.

 

[FDibbins]

Try this, copied down and cross...
=INDEX($D$3:$H$13,MATCH($M2&N$1,INDEX($A$3:$A$13&$B$3:$B$13,0),0),MATCH($L2,$C$1:$H$1,0)+MATCH($K$2,$D$2:$E$2,0)-1)