# Lookup from 2 horizontal cells??

#### jhucker

##### New Member
Hi,

I'm currently trying to figure out if I am able to do a lookup based on matching two pieces of horizontal information.

Sheet one (Order form) has colums headed Product, Store, Min, Max
Sheet two (Weighting) is a table with the product at the top and the store as the row header, with the max (which I am looking for) as the data.

Is there a formula I can use to do this? I thought an IF formula but I believe nesting is limited to 7, and I have 10 products and 173 stores.

Sheet one looks like this (but more rows)

 Product Store Min Max A 1 2 B 1 2 B 2 2 C 2 2 D 2 2 D 3 2 E 1 2 E 3 2 E 4 2 F 6 2 G 2 2 H 5 2 I 3 2 I 6 2 J 2 2

<tbody>
</tbody>

Sheet 2 looks a little like this (more rows)

 Store A B C D E F G H I J 1 2 4 4 3 4 2 3 3 3 4 2 2 4 3 3 4 2 4 2 4 2 3 4 4 3 3 2 2 4 3 4 2 4 3 2 2 3 3 2 2 3 2 3 5 4 3 2 3 2 2 3 3 2 2 6 2 3 2 3 2 2 3 3 3 2

<tbody>
</tbody>

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Are you saying, "Find the max where the store and the product match"?

If so, here is your solution.

Code:
``[COLOR=#574123]=INDEX([/COLOR][COLOR=Blue]Sheet1!\$F\$1:\$P\$7,MATCH([COLOR=Red]Sheet1!B2,Sheet1!\$F\$1:\$F\$7,0[/COLOR]),MATCH([COLOR=Red]Sheet1!A2,Sheet1!\$F\$1:\$P\$1,0[/COLOR])[/COLOR][COLOR=#574123])[/COLOR]``

You can drag it down as far as you need.

Excel 2010
ABCDEFGHIJKLMNOP
1ProductStoreMinMaxStoreABCDEFGHIJ
2A12212443423334
3B12422433424242
4B22434433224342
5C22343223322323
6D22354323223322
7D32362323223332
8E124
9E322
10E423
11F622
12G224
13H523
14I324
15I623
16J222

</tbody>
Sheet1

Worksheet Formulas
CellFormula
D2=INDEX(Sheet1!\$F\$1:\$P\$7,MATCH(Sheet1!B2,Sheet1!\$F\$1:\$F\$7,0),MATCH(Sheet1!A2,Sheet1!\$F\$1:\$P\$1,0))

</tbody>

<tbody>
</tbody>

Replies
3
Views
132
Replies
11
Views
373
Replies
1
Views
115
Replies
5
Views
197
Replies
0
Views
143

1,203,687
Messages
6,056,747
Members
444,888
Latest member
Babi_mn

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