Choose function

[bluenight]

Hi,
I have to use the "choose" function in excel. However, my problem is that for one value, many lines correspond to it. For example:

1 MExico
2 Canada
3 à 45 USA
46 à 59 France
70 et + Belgium

So, if for say on my excel sheet I wrote 34, USA will appear. My problem is that I want to know if there's a short way (maybe use the function "If" inside the choose function) to write my choose function.

Thank you very much

[Paul]

Hi Bluenight, and welcome to the forum.

Can't say I understand your question. Can you try to explain a little differently what your data currently is, and what result you're expecting?

Thanks!

[zbor]

Something like this?

Book1.xls

Change number in H1

[DonkeyOte]

It's not clear whether your numbers and nations are in different columns or one column, zbor's file above assumes the latter.

For the sake of simplicity I would suggest however that if you can you should enter the min value of each range in 1 column and nation in column to right, eg:

Please Login or Register  to view this content.

Assuming above is in A:B then you can use a basic VLOOKUP to retrieve the nation appropriate to your value of choice, eg

Please Login or Register  to view this content.

(note use of 60 n/a to cater for fact there is no value specified for 60-69)

[bluenight]

Ok, i'll try to better explain my problem in a different way. Let's take this example

Rank 1 ... A+
Rank 2 ... A
Rank 3 to 6 ... A-
Rank 7 to 10 ... B+
Rank 11 to 42 ... B
Rank 42 and + ... B-

So, the ranks correspond to the score that a student get. So rank 1 correspond to the student that had the best score and so on... Therefore, using the choose function I have to write, depending of the rank, the result that the student will get. So if I write 34, the result "B" should appear. The problem is that i don't know how can i write something in my choose function so that the rank 11 to 42 corresponds to 31 values each giving a "B".

I hope it is clearer!
thank you

[bluenight]

I forgot... there are 2 columns... one column is rank... and the other one is result

[Paul]

You don't need CHOOSE, you can use LOOKUP.

=LOOKUP(what_to_lookup , array_of_values_to_search , array_of_values_to_return)

=LOOKUP(34,{1,2,3,7,11,42},{"A+","A","A-","B+","B","B-"})

The first array contains the lower-limit of each range in ascending order, and the result array contains the appropriate response for that value. The 34 can be a value, or a reference to a cell containing a value.

In this case, 34 would return "B".