# Lookup-style question, minimum price and seller.

#### Jaymond Flurrie

##### Well-known Member
Hi,

This looks pretty simple and I feel like I should know how to do this.

So given the range A1:D3 and user input B6, I'd need the formulas to B7 and B8.

In reality I have a few hundred stores and tens of thousands of items (with ID numbers as the column a - they are unique).

With one item this is trivial:
B7: =MIN(B2:D2)
B8: =INDEX(B1:D1;;MATCH(F7;B2:D2;0))
But what's the way to point to the row of that B2:D2 i.e. the item user wants?

Here's what I have thought:
The item x can be found easily with =MATCH(B6,\$A\$1:\$A\$3,0) - well, ok, assuming that item x exists there.
But then I would need a function that returns for me the B2:D2 range from which I'd get with the MIN-function the B7 result.
Then I'd need a MATCH-function to find the corresponding header row value.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### aRandomHelper

##### Active Member
Could this work?

Book1.xlsm
ABCDE
1ABCD
2item x112522
3item y1255521
4
5
6item x
7min price1
8storeA
Sheet7
Cell Formulas
RangeFormula
D7D7=MIN(OFFSET(1:1,MATCH(B6,A:A,0)-1,0))
D8D8=INDEX(1:1,MATCH(D7,OFFSET(1:1,MATCH(B6,A:A,0)-1,0),0))

#### Jaymond Flurrie

##### Well-known Member
Could this work?

Book1.xlsm
ABCDE
1ABCD
2item x112522
3item y1255521
4
5
6item x
7min price1
8storeA
Sheet7
Cell Formulas
RangeFormula
D7D7=MIN(OFFSET(1:1,MATCH(B6,A:A,0)-1,0))
D8D8=INDEX(1:1,MATCH(D7,OFFSET(1:1,MATCH(B6,A:A,0)-1,0),0))
Yes, seems to do exactly what I wanted, thank you

Replies
5
Views
71
Replies
7
Views
122
Replies
3
Views
231
Replies
0
Views
55
Replies
6
Views
300

1,129,529
Messages
5,636,853
Members
416,945
Latest member
Himu

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

### Which adblocker are you using?

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

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