Find Lowest value

[Muthukrishnan V]

Book1
	A	B	C	D	E
1	Excel 365 Find Lowest value and the relative names
2
3	Name	Amount		Name	Lowest Amount
4	ABC	7500		GHI	7300
5	DEF	7800		PQR	7300
6	GHI	7300
7	JKL	8300
8	MNO	7600
9	PQR	7300
Sheet1

 

[plettieri]

To find the lowest value and its relative names in Excel 365, you can use a combination of the MIN function, INDEX function, and MATCH function. Here are the steps:

1. Select the range of cells that contain the values you want to find the lowest value from.
2. Enter the following formula in a blank cell: =INDEX(range,MATCH(MIN(range),range,0)) Replace "range" with the actual range of cells you selected in step 1.
3. Press Enter to calculate the formula. This will return the name of the cell containing the lowest value in the range.
4. To find the next lowest value and its relative name, copy the formula from step 2 to the cell below, and change "MIN(range)" to "MIN(range excluding previous cell)". You can do this by manually adjusting the range or by using the OFFSET function to exclude the previous cell.
5. Repeat step 4 until you have found all the lowest values and their relative names.

Alternatively, you can use the SMALL function to find the nth smallest value and then use INDEX and MATCH to find its relative name. Here's how:

1. Select the range of cells that contain the values you want to find the lowest value from.
2. Enter the following formula in a blank cell: =SMALL(range,n) Replace "range" with the actual range of cells you selected in step 1, and "n" with the nth smallest value you want to find (e.g. 1 for the lowest value, 2 for the second lowest value, etc.).
3. Press Enter to calculate the formula. This will return the nth smallest value in the range.
4. Enter the following formula in a blank cell next to the formula from step 2: =INDEX(names,MATCH(nth smallest value,range,0)) Replace "names" with the range of cells that contain the names you want to match to the values in the original range.
5. Press Enter to calculate the formula. This will return the name of the cell containing the nth smallest value in the range.
6. Repeat steps 2-5 to find all the lowest values and their relative names.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	Name	Amount		Name	Lowest Amount
2	ABC	7500		GHI	7300
3	DEF	7800		PQR	7300
4	GHI	7300
5	JKL	8300
6	MNO	7600
7	PQR	7300
8
Main

Cell Formulas
Range	Formula
D2:E3	D2	=TAKE(SORT(A2:B100,2,1),COUNTIFS(B2:B100,MIN(B2:B100)))
Dynamic array formulas.

 

[fjns]

Hi, see the linked file for a possible solution...
Wrong cells visible on Google Drive, because Google Drive does not know the FILTER function. The formula works correctly with your Office 365.

The formula used in the table:
D4: =FILTER(A:B,B:B=MIN(B:B)) (Range:D4:E5)

Lowest.xlsx

 

[Muthukrishnan V]

Thank you Mr. Fluff sir and Mr fjns sir. Works great.
I am very grateful to the Forum.

 

[fjns]

Hi, I am very glad that the formula works.
If I have enough knowledge, I am happy to help at any time.

 

[Fluff]

Glad we could help & thanks for the feedback.