Excel Fomular_Index Match and Min Max

[kimudao]

Dear Guru,
I have some help as attachment.
I describe the problem in Excel file.
Please help.
Thanks.

[HansDouwe]

There are 2 possibilities:

Possibility 1:
Please try in M5 and copy down: (the zeros will be ignored)

Please Login or Register  to view this content.

Possibility 2
or remove the zeros (and leave the cells empty) or write something else alphanumeric in the cells.

[Bo_Ry]

Please try

=IFERROR(INDEX($E$4:$I$4,MATCH(SMALL(E5:I5,COUNTIF(E5:I5,0)+1),E5:I5,)),"NA")

[wk9128]

OFFICE 365 , formula drag down

Please Login or Register  to view this content.

Pls try this VBA Code

Cell K5 formula , Drag down

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[kimudao]

Hi
I change to use small function instead and this is work and ignore 0
But i will tried your fomular too
Thank

[kimudao]

Hi Bo Ry,
this formula not work due to if find duplicate value need to index match as BBB&CCC to show from G10&I10 , I PUT comment at the column N.
how to do?

[wk9128]

Hi kimudao Since your OFFICE version is lower than 2019, the CONCAT and TEXTJOIN functions cannot be used. Only VBA custom functions can be provided to solve your problem

[kvsrinivasamurthy]

In K5 copied down

Please Login or Register  to view this content.

[kimudao]

hI kv
thank for your reply,
but i don't want to show price i have to show NAME IN ROW E4 SUCH AS AAA,BBB,CCC

[kvsrinivasamurthy]

Try. In K5

Please Login or Register  to view this content.

[kimudao]

Hi KV,
I found G10 & I10 is same price and i need to show Both as BBB&CCC
And also Row 9 same price for 3 company need to show AAA&BBB&CCC
How possible to show like this? in case found duplicate >=2 value , if no duplicate price, formula is ok working.
Thanks so much.

[windknife]

a ugly formula

K5

Please Login or Register  to view this content.

copied down.

[Bo_Ry]

Please try

=IFERROR(SUBSTITUTE(TRIM(REPT(E$4,E5=SMALL(E5:I5,COUNTIF(E5:I5,0)+1))&" "&REPT(G$4,G5=SMALL(E5:I5,COUNTIF(E5:I5,0)+1))&" "&REPT(I$4,I5=SMALL(E5:I5,COUNTIF(E5:I5,0)+1)))," ","&"),"NA")

[kimudao]

Hi BO RY,
E9 G9 I9 RESULT IN K9 still incorrect should show AAA&BBB&CCC other is correct.
can u pleaes?

[kimudao]

Hi , windknife
thank so much , your formula almost correct, sorry i put wrong result for E9,G9,I9 K9 = AAA&BBB&CCC due to this row is same price in 3 column so should be all best price in 3 name .sorry i put wrong result.
thanks so much.

[Phuocam]

Try this in M5:

=IFERROR(AGGREGATE(15,6,E5:I5/(E5:J5<>0),1),"NA")

in K5:

=IF(M5="NA","NA",MID(IF(E5=M5,"&"&$E$4,"")&IF(G5=M5,"&"&$G$4,"")&IF(I5=M5,"&"&$I$4,""),2,1000))

[kimudao]

Hi BORY & Windknife
oh sorry, i am incorrect, that row9 have duplicate only 2 value, and if change duplicate all 3 is work for yours formula.
big thank u both,
and all members too help me.
it 's very perfect.but i would like to know formular from BORY for rept, how can use and when will use this function? i never use at all but i will tried to google for this function.
thanks so much.

[windknife]

You are welcome.

Bo_Ry's formula is very elegant which I should learn it.

-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.

[kimudao]

Hi Phuocam Phuocam,
Also your formula is work and other style to create with IF and other Excel function.i will learn your formula.
thanks so much.

[kimudao]

Hi BO RY,
A bit not to show in case I change name in column E4 from AAA to AA COM , this name have blank between AA COM and make fomular put & as AA&COM, i need to show same AA COM.
Please kindly help to edit this?
Thanks.

[kimudao]

Hi ALL,
all formula from VN , TW is ok if in case name have space like AA A.
But for BO RY Formula still a bit incorrect. if anyone can solve from TH Formula .please let me know.
Thanks.

[windknife]

Try to use full-width space to instead of half-width space.

K5

Please Login or Register  to view this content.

[Phuocam]

Try change to:

Please Login or Register  to view this content.

[kimudao]

Hi Phuocam & windknife windknife
work for both formula , big thanks and very awesome.
thanks all of you guys.

[kimudao]

Hi Windknife
a bit not correct for your fomular will show && infornt of name.
please see in attachment.

[windknife]

Very strange, in my excel version is correct. It may be that I am using the Chinese version. You can use Phuocam's formula to solve it.

[kimudao]

Hi Windknife.
yes, i use firstly.
thanks u too.