Formula to Get the Max of a column and its definition

  • Thread starter Thread starter Shadow_Otixz
  • Start date Start date
S

Shadow_Otixz

Let's say I have two columns...
From: Sheet1
Column Z = Product Name
Column B = Reject Quantity

Problem: Using Sheet2, How can I have the Max value of the Column
(from Sheet1) and have its Product Name from Column Z? Remember, th
Sheet1 table is modified every now and then so the max value of B migh
be different in every minute.

Desired End Result:
In Sheet2:

Reject Quantity Product Name
10 Dent

Please help me on this....

Otix
 
In Sheet2, try ..

In B2: =INDEX(Sheet1!B:B,MATCH(MAX(Sheet1!B:B),Sheet1!B:B,0))
In Z2: =INDEX(Sheet1!Z:Z,MATCH(MAX(Sheet1!B:B),Sheet1!B:B,0))

B2 returns the max value of col B in Sheet1, whilst
Z2 returns the corresponding product name from col Z in Sheet1

(above assumes there's no ties for max values in col B in Sheet1)
 
If there's the possibility of ties in the max value,
perhaps try this set-up instead ..

In Sheet1
------------
Use an empty col to the right of the data, say col AA

Put in AA2: =IF(B2="","",B2-ROW()/10^10)
Copy down as many rows as there is data in col B

(Col AA will function as an arbitrary tie-breaker)

In a new Sheet3
-----------------------
Put in A1:B1, the labels: RejectQty ProdName

Put in A2:
=IF(ISERROR(MATCH(LARGE(Sheet1!AA:AA,ROW(A1)),Sheet1!AA:AA,0)),"",INDEX(Shee
t1!B:B,MATCH(LARGE(Sheet1!AA:AA,ROW(A1)),Sheet1!AA:AA,0)))

Put in B2:
=IF(ISERROR(MATCH(LARGE(Sheet1!AA:AA,ROW(A1)),Sheet1!AA:AA,0)),"",INDEX(Shee
t1!Z:Z,MATCH(LARGE(Sheet1!AA:AA,ROW(A1)),Sheet1!AA:AA,0)))

Select A2:B2, copy down say, to B6

In A2:B6 will be extracted the Top* 5 Reject Qty values and
the corresponding product names from cols B and Z in Sheet1
(*In descending order)

Any ties in the largest, or in the 2nd largest, 3rd largest values, etc
within col B in Sheet1 will be extracted** into the range A2:B6
(**ties will be ordered arbitrarily depending on their row position
in Sheet1, i.e. those in higher rows will appear above lower rows)

Copying A2:B2 down to B6 will return "Top 5"
Just copy down more rows to extract as desired: Top 10, Top 20, etc
 
This questions was already asked and answered yesterday. See 11/1st
post subject Lookup question.

in a2 of sheet 2 put:
=INDEX(Sheet1!B:B,MATCH(MAX(Sheet1!B:B),Sheet1!B:B,0))

in b2 of sheet 2 put:
=INDEX(Sheet1!Z:Z,MATCH(MAX(Sheet1!B:B),Sheet1!B:B,0))
 
Back
Top