MAX KIP no based on max amount

[majidsiddique]

Hi all,
can anybody help me to solve this?
i want to find MAX KIP no based on max value of item. please see the file.

[PaulM100]

Array entered for Max KIP: =MAX(IF(Sheet2!$D$2:$D$13=Sheet1!F2,Sheet2!$B$2:$B$13))
For Max value, change the column references.

[majidsiddique]

Hi PaulM100,
thanks for reply, in your formula based on F2 value sheet1. i put this value manually for understanding not for criteria. if i remove this formula failed. KIP will be searched based on sheet2 column D rate to identify which is the highest value of item comes in which KIP.
MAX KIP and MAX value Rate Amount AUTO find.
see the result.

[majidsiddique]

Any solution?

[Pepe Le Mokko]

Personally, I do not understand what you are trying to achieve

[majidsiddique]

Hi Pepe Le Mokko,
please see the attach sheet. i use formula column b sheet1 to pick KIP no from Sheet2 and another formula use to pick the value of item column E sheet1. But result is differ in column C and F its means KIP No is not OK. KIP 406 shows max value of item MD-5867-3M00 in sheet2 714.622222. same of others. i want first to pick KIP to MAX value of Item then value of items shows.

[Bo_Ry]

I do not quite understand but please try at B2 and drag down

=INDEX(Sheet2!$B$2:$B$15,MATCH(MAX(INDEX(ISNUMBER(SEARCH(D2,Sheet2!$C$2:$C$15))*Sheet2!$D$2:$D$15,)),INDEX(ISNUMBER(SEARCH(D2,Sheet2!$C$2:$C$15))*Sheet2!$D$2:$D$15,),))

[majidsiddique]

Hi Bo_Ry,
thanks for reply, i use this formula for sample file attached, its works perfect excellent, BUT when i use to whole data its fail why?
can you tell me?
please see the again attached file sheet2 complete data. i just increase the range of rows. see the formula in b2 sheet1

[Bo_Ry]

Please try

=INDEX(Sheet2!$B$2:$B$12000,MATCH(AGGREGATE(14,6,Sheet2!$D$2:$D$12000/(D2=Sheet2!$C$2:$C$12000),1),INDEX(Sheet2!$D$2:$D$12000/(D2=Sheet2!$C$2:$C$12000),),))

[majidsiddique]

Hi Bo_Ry,
its fantastic, really cheers, Can you fix it by date?
i have to work within date range, it is available in sheet2 column A. date range start from 1/1/2017 to 8/12/2018 so given date range formula pick KIP. my data date range from 7/7/2015. do not consider formula before given date range. see the snap for one example.
snap show you KIP No before the date range.
rest of all KIP pick by formula are OK, well done.
sorry for late communicate this.
Thanks for your help

[Bo_Ry]

Please try
C2
=INDEX(Sheet2!$B$2:$B$12000,MATCH(AGGREGATE(14,6,Sheet2!$D$2:$D$12000/(D2=Sheet2!$C$2:$C$12000)/(Sheet2!$A$2:$A$12000>=--"1/1/2017")/(Sheet2!$A$2:$A$12000<=--"8/12/2018"),1),INDEX(Sheet2!$D$2:$D$12000/(D2=Sheet2!$C$2:$C$12000)/(Sheet2!$A$2:$A$12000>=--"1/1/2017")/(Sheet2!$A$2:$A$12000<=--"8/12/2018"),),))

[majidsiddique]

you are super,
really fantastic job, thanks a lot BOSS.