Formula Help: Find the largest number that ends with a certain digit

[Chris The Rock]

I know the =LARGE() function finds the kth largest number in an array.

I need to build a formula that finds the kth largest number in an array - when the number ends with a specified digit.

Data like this:
11
51
31
21
41
would give the result 51, if I'm looking for numbers ending in 1.
This:
7
27
47
87
would return 87, if I want a number ending in 7.

Finally, data like this:
11
23
34
89
27
16
would return 16, if I'm searching for the largest number ending in 6.

I'd like to do this without writing a custom function in VBA.

Any suggestions?

 

[schielrn]

Maybe try this:

=LARGE(IF(RIGHT(G1:G100,1)="6",G1:G100),1)

This needs to be confirmed with control+shift+enter and not just enter.

Hope that helps.

 

[Chris The Rock]

Maybe try this:

=LARGE(IF(RIGHT(G1:G100,1)="6",G1:G100),1)

This needs to be confirmed with control+shift+enter and not just enter.

Hope that helps.

An array formula is almost certainly what I need, but that formula doesn't work because it's comparing text values (RIGHT) with numeric values (LARGE). However, it gives me a good place to start.

Thanks!

 

[Chris The Rock]

An array formula is almost certainly what I need, but that formula doesn't work because it's comparing text values (RIGHT) with numeric values (LARGE). However, it gives me a good place to start.

Thanks!

I just stuck a "1*" in front of the IF statement and it does just what I want.

 

[schielrn]

An array formula is almost certainly what I need, but that formula doesn't work because it's comparing text values (RIGHT) with numeric values (LARGE). However, it gives me a good place to start.

Thanks!

Not sure what you mean? I put ="6" so it looked at a text value, but you could also use:

=LARGE(IF(RIGHT(G1:G100,1)+0=6,G1:G100),1)

But everything in the list would need to be a number?

 

[SydneyGeek]

maybe:

=LARGE(IF(MOD(G1:G100,10)=6,G1:G100),1)

Confirm with Ctrl+Shift+Enter

Denis

 

[Chris The Rock]

Not sure what you mean? I put ="6" so it looked at a text value, but you could also use:

=LARGE(IF(RIGHT(G1:G100,1)+0=6,G1:G100),1)

But everything in the list would need to be a number?

Everything works, but it was necessary for everything in the list to be a number.

Thank you everyone for jogging my memory and helping me solve this mystery.