Vlookup without false

• Hi,

I have a small problem. When I use Vlookup and don't put "False" in the code then Vlookup find a match that is lower and nearest to the number I want to lookup. How can I make it so it will give me the number that is nearest but up? Meaning that if I write 220 then the result should be 6 and not 5.

Please take a look at my exsample.

Alring

Files

• Re: Vlookup without false

Hi Alring

Based purely on your example use;

=INDEX(\$C\$6:\$D\$13,MATCH(A1,\$C\$6:\$C\$13,-1),2)

....and sort your lookup table in Descending order.

• Re: Vlookup without false

Hi,

Please find attached my solution which matchs with your needs except no vlookups.

Biz

Files

• Re: Vlookup without false

Hi Dave

When I use your formula then I get #N/A as result.

Alring

• Re: Vlookup without false

Hi,

It will unless your sort your Lookup table in Descending order by the left-most column

• Re: Vlookup without false

Quote from Dave Hawley

Hi,

It will unless your sort your Lookup table in Descending order by the left-most column

Hi David,

Your formula does work. I learnt another way of solving this issue. Thank you.

Biz

• Re: Vlookup without false

Hi

Dave>> It was my mistake. Your formula works perfect. Sorry. And THANK you for the help.

Biz>> Your formula works perfect also. THANK you for the help.

Now I just have the problem to pick one of them. But that's my problem.

THANK you both.

Alring

• Re: Vlookup without false

Hi,

Two ways of doing a task is better then one way. Anyways, enjoy.

Biz

• Re: Vlookup without false

That's what the forum is all about, that is, more than one way to skin a cat

• Re: Vlookup without false

Hi again

I’m happy to tell you that I have found a third way to skin the cat.

Since I don’t want to change the order to Descending and I also want that if the number I write in A1 is present in the array then I have changed the formula. I have used Biz’s solution and combined it with Counta. Now the formula works as I want it to.

Both Dave and Biz THANK you for the help and guide dings.

Please take a look at the example to see the solution.

Alring

PS:. Thank you for a perfect forum.

Files

• Re: Vlookup without false

Hi Alring,

Very good solution buddy!

Biz

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!