Lookup-style question, minimum price and seller.

[Jaymond Flurrie]

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.

 

[aRandomHelper]

Could this work?

Book1.xlsm
	A	B	C	D	E
1		A	B	C	D
2	item x	1	12	52	2
3	item y	12	55	5	21
4
5
6		item x
7	min price			1
8	store			A
Sheet7

Cell Formulas
Range	Formula
D7	D7	=MIN(OFFSET(1:1,MATCH(B6,A:A,0)-1,0))
D8	D8	=INDEX(1:1,MATCH(D7,OFFSET(1:1,MATCH(B6,A:A,0)-1,0),0))

 

[Jaymond Flurrie]

Could this work?

Book1.xlsm
	A	B	C	D	E
1		A	B	C	D
2	item x	1	12	52	2
3	item y	12	55	5	21
4
5
6		item x
7	min price			1
8	store			A
Sheet7

Cell Formulas
Range	Formula
D7	D7	=MIN(OFFSET(1:1,MATCH(B6,A:A,0)-1,0))
D8	D8	=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