Lookup Formula Explanation Please

[Arts]

Hi all

I have the below formula which gives me the last value in a range :
=LOOKUP(2,1/(A1:A10),A1:A10)

56		24
78
65
74
45
99
24

My question is I was under the impression when using LOOKUP the numbers needed to be in ascending order so in order for the below to work this needs to be in the order 1,2,3,4,5 but for the above this isn't the case and returns what I need and was wondering why.

Any help on this is appreciated as always.

 

[LegendaryAxe]

It doesn't need to be in ascending order. That formula is looking for the number 2 in a range of numbers smaller than 2 (dividing 1 by the numbers makes all the numbers look something like this:

This results in it returning the last item in the range.

 

[Arts]

hmmm

The below isn't in ascending order and the LOOKUP doesn't return the correct value as it did if it was :

=LOOKUP(I2,G2:G6,H2:H6)

5	Maths	3	Physics
3	Science
1	Physics
4	Chemistry
2	Drama

 

[LegendaryAxe]

That's because in that instance you're not creating a range where 1 is divided by all the values. Doing that makes all the values in the lookup range less than 1, so when searching for a 2 (or a value greater than 2), it won't find it, and instead gives you the last value in the return range.

 

[Arts]

Ok so there is an aspect within the LOOKUP function the data needs to be in ascending order, I thought this was a necessity whenever it was being used.

 

[Arts]

Have been over this but still do not fully understand the logic, any additional input/breakdown would be welcome. Perhaps if I ask this next question and then build my knowledge from there.

The below doesn't seem to work as the lookup_vector within the LOOKUP function isn't in ascending order, correct ?

=LOOKUP(I2,G2:G6,H2:H6)

5	Maths	3	Physics
3	Science
1	Physics
4	Chemistry
2	Drama

My question would then be, When is an ascending order necessary within the LOOKUP formula?

Why does in one aspect to return the correct value of 3 (I get Physics and not Science ) this needs to be in ascending order but in order to attain the last value an ascending order isn't required.

* I shall log on tomorrow to check back in so not to come across as rude should I not reply.

 

[LegendaryAxe]

The 2,1/(array) formula is more or less a trick to get the last value. You're giving Excel a number of values lower than 2, so it cannot find 2 or a number greater than 2 and ends up giving you the last value in the result array.

 

[Arts]

Just caught your response, thanks that helps explain it!

 

[LegendaryAxe]

Just caught your response, thanks that helps explain it!

Glad I could help.