# Complex Lookup Function

#### seguin85

##### Active Member
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!!

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### FDibbins

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

#### seguin85

##### Active Member
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

##### Well-known Member
Still does not explain how to chose either column D or column G?

#### seguin85

##### Active Member
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

##### Well-known Member
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)

Replies
2
Views
597
Replies
1
Views
692
Replies
4
Views
673
Replies
11
Views
573
Replies
0
Views
521

1,195,848
Messages
6,011,949
Members
441,657
Latest member
Diupsy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back