VLOOKUP or

[N0t Y0urs]

Hi, I am currently working with Google sheets on my phone so restricted with how to show what I am after.

I want L4 to reference Q3 and return a value from the range FE25:FF63

Basically the values in column FF increase but there’s no pattern to the increase in the respective FE row is the value I want to return

An example is:

FE25 = 0.02 FF25 = 50
FE26 = 0.04 FF26 = 75
Skip a few
FE35 = 0.20 FF35 = 2400
FE36 = 0.35 FF36 = 5000
Skip a few more
FE62 = 100 FF62 = 75000
FE63 = 125 (max value) FF62 = >80000

Therefore is Q3 equals 45 the L4 would be 0.02

Then the next row would be Q4 equals 72.50 L5 would be 0.04

The skip to row 250 and Q249 is 74699 L250 returns 100 while Q250 is 93250 L251 returns the value of 1250

Is a v lookup what I need or is it something else. Any suggestions are much appreciated

 

[Legacy 143009]

Swap FE with FF.

L4=VLOOKUP(Q3, FE:FF, 2, 1)

 

[RoryA]

VLOOKUP or similar won't work because your values in column FF appear to be the upper limit for the value in FE, and for a nearest match VLOOKUP you need them to be the lower limit - eg FF25 would need to be 0 and FF26 would need to be say 50.01. Or you can use something like:

=INDEX(FE25:FE63,MATCH(TRUE,FF25:FF63>=Q3,0))

 

[N0t Y0urs]

Thank you both for your suggestions. I tried Flashbond’s suggestion and it worked for me so thanks again

 

[Legacy 143009]

Glad it did help!

 

[jasonb75]

Then the next row would be Q4 equals 72.50 L5 would be 0.04

With the example provided, @Flashbond's suggestion would return 0.02 for 72.50, it would not return the 0.04 that you say it should be.

If it does work correctly then swapping the 2 columns was not necessary, you could have simply used

=LOOKUP(Q3,FF:FF,FE:FE)

With the original layout.