Lookup-style question, minimum price and seller.

Jaymond Flurrie

Well-known Member
Joined
Sep 22, 2008
Messages
870
Hi,

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

1618217149058.png


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
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
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))
 
Solution

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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
Top