Alter Vlookup formula so it looks up from the bottom.

[danbates]

Hi,

Please can someone help me?

I have a Vlookup formula and I would like it altered so it looks up from the bottom to the top instead of the other way.

The list gets added to every day. At the moment this is the range of the list A4:D730

Here is my formula:

=IFERROR(VLOOKUP(B:B,Data!A:D,2,),"")

Any help would be appreciated.

Thanks

Dan

 

[Eric W]

Try:

=IFERROR(LOOKUP(2,1/(Data!$A$4:$A$1000=B2),Data!$B$4:$B$1000),"")

Note that I used 1000 as the last row to search, something beyond your 730 range.

 

[danbates]

Hi Eric,

It works thank you but the only thing is, when the B2 cell is blank it leaves a 0 instead of being blank where the formula is.

Can anything be done to alter it so it's blank?

Thanks again

Dan

 

[AC PORTA VIA]

Select column B go to Format cells -Custom -under Type: replace General with 0;-0;;@

 

[danbates]

That's brilliant, thank you.