# How to return a number based on ranking?

#### excelos

##### Active Member
Hello

I have three formulas that calculate three different numbers in the format: "23 USA orders", "53 EU orders", "36 ASIA orders".

How can I return the first biggest and second biggest from the above?

Thanks!

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
L

#### Legacy 436357

##### Guest
Hi if your data is in column B this may work:

=MAX(IFERROR(LEFT(B:B,3)*1,0))

Press CTRL+SHIFT+ENTER to make it an array.

I used a 3 in case the orders go to 100-999

Last edited by a moderator:
L

#### Legacy 436357

##### Guest
Hi again,

It is better to separate the data into columns using Text to Columns and choose deliminated and check off by spaces. Then you can get the largest and 2nd, 3rd, 4th up to however many you want.

Sheet1

 B C D E F 2 23 USA orders 23 USA orders 53 3 53 EU orders 53 EU orders 36 4 36 ASIA orders 36 ASIA orders 23

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:157.6px;"><col style="width:65.6px;"><col style="width:48px;"><col style="width:66.4px;"><col style="width:80.8px;"></colgroup><tbody>
</tbody>

 Cell Formula F2 =IFERROR(LARGE(\$C\$2:\$C\$20,ROW(1:1)),"") F3 =IFERROR(LARGE(\$C\$2:\$C\$20,ROW(2:2)),"") F4 =IFERROR(LARGE(\$C\$2:\$C\$20,ROW(3:3)),"")

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Last edited by a moderator:

#### excelos

##### Active Member
None of these solutions answers my question.

I suppose I need to use the LARGE function as I want to return the first and second largest.

However, the data I want to sort and then return, are a mixture of numbers+text.

L

#### Legacy 436357

##### Guest
If you look at my 2nd reply in column F of the example it shows the largest and then the 2nd and 3rd (which is additional to show how it works as you copy the formula down.

Replies
5
Views
54
Replies
4
Views
189
Replies
7
Views
165
Replies
1
Views
44
Replies
0
Views
43

1,109,420
Messages
5,528,668
Members
409,829
Latest member
CFreeamaz

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...