Lookup from table in descending order

[kent97]

Dear all,

ScreenHunter_005.jpg

Whenever I input a figure at E4, the name with Mark equal or smaller than E4 are list out in E5:E8 in descending order. What is the formula please?

[tamthat]

Dear all,

Attachment 484188

Whenever I input a figure at E4, the name with Mark equal or smaller than E4 are list out in E5:E8 in descending order. What is the formula please?

Yes, it's:

Please Login or Register  to view this content.

[kent97]

Yes, it's:

Please Login or Register  to view this content.

Hi,

1. The results should be David, Peter, Jane & Judy. But your formula comes out David, Peter Judy & Mary.

2. If The mark of David is 71, the results should be Peter, Jane, Judy & Tom. Your formula results in all "N/A"

[tamthat]

Hi,

1. The results should be David, Peter, Jane & Judy. But your formula comes out David, Peter Judy & Mary.

2. If The mark of David is 71, the results should be Peter, Jane, Judy & Tom. Your formula results in all "N/A"

Yes, i see

[tamthat]

Hi,

1. The results should be David, Peter, Jane & Judy. But your formula comes out David, Peter Judy & Mary.

2. If The mark of David is 71, the results should be Peter, Jane, Judy & Tom. Your formula results in all "N/A"

Please Login or Register  to view this content.

Ctrl + Shift + Enter!

[FlameRetired]

Try this array entered formula (Ctrl + Shift + Enter)

Please Login or Register  to view this content.

[FlameRetired]

My previous formula works for 70 but fails for 99. This one does not.

With a helper column in G5:G8 this array formula

Please Login or Register  to view this content.

then this array entered formula in F5:F8

Please Login or Register  to view this content.

With 70 as the criteria:

Row\Col	B	C	D	E	F	G
2	Data			Result
3
4	Name	Mark		70
5	Alice	6		David	David	70
6	Tom	27		Peter	Peter	66
7	Jane	34		Jane	Jane	34
8	David	70		Judy	Judy	34
9	Peter	66
10	Judy	34
11	Mary	5
12	Kent	99

With 99 as the criteria:

Row\Col	B	C	D	E	F	G
2	Data			Result
3
4	Name	Mark		99
5	Alice	6		David	Kent	99
6	Tom	27		Peter	David	70
7	Jane	34		Jane	Peter	66
8	David	70		Judy	Jane	34
9	Peter	66
10	Judy	34
11	Mary	5
12	Kent	99

[soledad]

Here it is
Formula for cell E5

Please Login or Register  to view this content.

Confirm with the Keystroke CTRL+SHIFT+ENTER
Copy down

[tamthat]

Here it is
Formula for cell E5

Please Login or Register  to view this content.

Confirm with the Keystroke CTRL+SHIFT+ENTER
Copy down

Great! Thanks!

[kent97]

Please Login or Register  to view this content.

Ctrl + Shift + Enter!

Yours still has some problem??

[kent97]

Hi FlameRetired,

Thanks so much for your patience and detailed answer.

[kent97]

Here it is
Formula for cell E5

Please Login or Register  to view this content.

Confirm with the Keystroke CTRL+SHIFT+ENTER
Copy down

I think I can never write such a complicated formula by myself, thank you soledad

[FlameRetired]

Thank you for the feedback.

[kent97]

Hi,

By using this formula,

=LOOKUP(1,1/(LARGE((C$5:C$12<=E$4)*RANK(C$5:C$12,C$5:C$12,1)*ROW($12:$12)-ROW($5:$12),ROWS($1:1))=RANK(C$5:C$12,C$5:C$12,1)*ROW($12:$12)-ROW($5:$12)),B$5:B$12)

I can find out the name with Mark equal or smaller than E4 are list out in E5:E8 in descending order.

How about if I want to find out the name with Mark equal or LARGER than E4 and list out in E5:E8 in ASCENDING order.

ScreenHunter_007.jpg

[FlameRetired]

soledad appears to be logged off at this time.

I tried doing this with soledad's formula and it works with one possible glitch.

Please Login or Register  to view this content.

With 34 in 'Mark' it returns the correct people, but with Jane and Judy in the same relative order as when 'Mark' was equal or SMALLER than E4. Try it (array entered) and see what I mean.

Presently I don't know how to resolve this part of soledad's formula. Will it work just the same?

[kent97]

soledad appears to be logged off at this time.

I tried doing this with soledad's formula and it works with one possible glitch.

Please Login or Register  to view this content.

With 34 in 'Mark' it returns the correct people, but with Jane and Judy in the same relative order as when 'Mark' was equal or SMALLER than E4. Try it (array entered) and see what I mean.

Presently I don't know how to resolve this part of soledad's formula. Will it work just the same?

Hi FlameRetired,

That minor glitch doesn't matter too much. The question I raise up is a simplified version, I have to modify the formula to fit my purpose. I feel faint when looking at it @@@
Thank you so much for your kind assistance.

[FlameRetired]

You are welcome, but soledad did all the "heavy lifting" on this one.

[soledad]

Hi,
How about if I want to find out the name with Mark equal or LARGER than E4 and list out in E5:E8 in ASCENDING order.

In this case you use the following formula

Please Login or Register  to view this content.

You can replace the function SMALL by function LARGE if you wish to order by descending
Also, you can rewrite the formula in case Mark equal or SMALLER than E4 as following

Please Login or Register  to view this content.

Hope it helps

[tamthat]

soledad appears to be logged off at this time.

I tried doing this with soledad's formula and it works with one possible glitch.

Please Login or Register  to view this content.

With 34 in 'Mark' it returns the correct people, but with Jane and Judy in the same relative order as when 'Mark' was equal or SMALLER than E4. Try it (array entered) and see what I mean.

Presently I don't know how to resolve this part of soledad's formula. Will it work just the same?

You should attach your file. I do not think that is the problem

[kent97]

In this case you use the following formula

Please Login or Register  to view this content.

You can replace the function SMALL by function LARGE if you wish to order by descending
Also, you can rewrite the formula in case Mark equal or SMALLER than E4 as following

Please Login or Register  to view this content.

Hope it helps

I have tried as what you did before, but I didn't replace the "+" sign to "-", so I got the wrong result.
Thanks a lot!