Find corresponding column based off lowest number in a different column

[japow]

Hi everyone,

I'm aiming for a formula to split out the minimum option based off numerous conditions; although instead of returning the numerical value I want to know the corresponding value in a text based column.

I'm thinking a combination of index/match/min/arrayformula/xlookup is required.

I've created a table below to help articulate what I'm after. I have a data source table, and need the formula to be separate.

Date source:

Product	Country	Supplier	Price
ABC	China	XMC Industries	$5
ABC	China	Shanghai INT	$2
ABC	Singapore	CAB INT	$15
ABD	Singapore	LAB Tech	$4.5
ABD	Singapore	CAB INT	$6
ABD	China	XMC Industries	$15

Table with incomplete formula:

Product	Singapore	China
ABC	CAB INT	FORMULA HERE TO FIND THE LOWEST PRICE SUPPLIER FOR ABC
ABD	LAB Tech	FORMULA HERE TO FIND THE LOWEST PRICE SUPPLIER FOR ABD

I hope this isn't sacrilege but I use Google Sheets as much as Excel so some formulas like aggregate won't work for me. Would really appreciate any help!

 

[Peter_SSs]

Welcome to the MrExcel board!

I use Google Sheets as much as Excel so some formulas like aggregate won't work for me.

I don't know about Google sheets, but for excel you could try this (allows for if multiple suppliers have equal lowest price)

22 10 06.xlsm
	A	B	C	D	E	F	G	H
1	Product	Country	Supplier	Price($)		Product	Singapore	China
2	ABC	China	XMC Industries	5		ABC	CAB INT	Shanghai INT
3	ABC	China	Shanghai INT	2		ABD	LAB Tech, XXX	XMC Industries
4	ABC	Singapore	CAB INT	15
5	ABD	Singapore	LAB Tech	4.5
6	ABD	Singapore	CAB INT	6
7	ABD	China	XMC Industries	15
8	ABD	Singapore	XXX	4.5
Lowest

Cell Formulas
Range	Formula
G2:H3	G2	=LET(f,SORT(FILTER($C$2:$D$8,($A$2:$A$8=$F2)*($B$2:$B$8=G$1)),2),TEXTJOIN(", ",,FILTER(INDEX(f,0,1),INDEX(f,0,2)=INDEX(f,1,2))))

 

[Kerryx]

ok thats one i am adding to my collection

 

[japow]

Ahh thanks for your help Peter, this is great for Excel, but sadly '=LET' does not work for Google Sheets. Appreciate your help nonetheless. It's my fault for posting here after all.
Would be great if you could still keep this open as I do believe it's possible with a formula that works in Sheets.

 

[Peter_SSs]

sadly '=LET' does not work for Google Sheets.

If that is the only problem, then you could try the 'full' version of the same formula.

BTW, if you are only looking for Google Sheets solutions, then your question should be asked, not in the Excel Questions forum but in the General Discussions and Other Applications forum.
Please keep that in mind for the future.

22 10 06.xlsm
	A	B	C	D	E	F	G	H
1	Product	Country	Supplier	Price($)		Product	Singapore	China
2	ABC	China	XMC Industries	5		ABC	CAB INT	Shanghai INT
3	ABC	China	Shanghai INT	2		ABD	LAB Tech, XXX	XMC Industries
4	ABC	Singapore	CAB INT	15
5	ABD	Singapore	LAB Tech	4.5
6	ABD	Singapore	CAB INT	6
7	ABD	China	XMC Industries	15
8	ABD	Singapore	XXX	4.5
Lowest (2)

Cell Formulas
Range	Formula
G2:H3	G2	=TEXTJOIN(", ",,FILTER(INDEX(SORT(FILTER($C$2:$D$8,($A$2:$A$8=$F2)*($B$2:$B$8=G$1)),2),0,1),INDEX(SORT(FILTER($C$2:$D$8,($A$2:$A$8=$F2)*($B$2:$B$8=G$1)),2),0,2)=INDEX(SORT(FILTER($C$2:$D$8,($A$2:$A$8=$F2)*($B$2:$B$8=G$1)),2),1,2)))

 

[Peter_SSs]

.. Another possibility

22 10 06.xlsm
	A	B	C	D	E	F	G	H
1	Product	Country	Supplier	Price($)		Product	Singapore	China
2	ABC	China	XMC Industries	5		ABC	CAB INT	Shanghai INT
3	ABC	China	Shanghai INT	2		ABD	LAB Tech, XXX	XMC Industries
4	ABC	Singapore	CAB INT	15
5	ABD	Singapore	LAB Tech	4.5
6	ABD	Singapore	CAB INT	6
7	ABD	China	XMC Industries	15
8	ABD	Singapore	XXX	4.5
Lowest (3)

Cell Formulas
Range	Formula
G2:H3	G2	=TEXTJOIN(", ",1,IF($A$2:$A$8=$F2,IF($B$2:$B$8=G$1,IF($D$2:$D$8=MINIFS($D$2:$D$8,$A$2:$A$8,$F2,$B$2:$B$8,G$1),$C$2:$C$8,""),""),""))

 

[japow]

Ah thanks Peter, i'll keep that in mind next time. Appreciate all the help, sadly Google Sheets is not up for this!

 

[Peter_SSs]

I have moved the thread to the correct forum for Google Sheets questions.

sadly Google Sheets is not up for this!

What about this in G2?
=ArrayFormula(TEXTJOIN(", ",1,IF($A$2:$A$8=$F2,IF($B$2:$B$8=G$1,IF($D$2:$D$8=MINIFS($D$2:$D$8,$A$2:$A$8,$F2,$B$2:$B$8,G$1),$C$2:$C$8,""),""),"")))

 

[japow]

Thanks.

Sadly that didn't work either. You can view it in Google Sheets here. I believe it might have to be a index(match(min type formula required.

 

[Peter_SSs]

Sadly that didn't work either. You can view it in Google Sheets here.

In that sheet you have the formula from post #6, not the formula from post #8