0

I have a table:

table in which I reference data from a dataset using a LOOKUP function. I can reference columns(such as new_deaths) which have the latest(non-blank) data just fine. However, for some columns such as Total Vaccinations, the latest data is not available so when I try to use the LOOKUP function, I get a 0 (blank) instead. I would like to skip the blank values and return the first numeric value.

Picture clarification: I reference another sheet that's called World.

The column New Deaths returns the latest values since the last entry in the World sheet is not blank. For the Total vaccinations in millions it returns a 0. The raw data can be found on OurWorldInData.org

Improve this question
1

1 Answer 1

Reset to default
0

I suggest you try this formula =IFERROR(LOOKUP(1,0/(($A2=World!C:C)*(World!AI:AI<>"")),World!AI:AI),"")

enter image description here

enter image description here

Improve this answer
2
  • Thank you Emily. This formula worked beautifully. If I may ask, how does this formula work? and is it possible to write a formula which takes the resulting total vaccination number to report the date as well?
    – Moe
    Commented May 4, 2021 at 15:24
  • @Moe This formula is to find the value that meet multiple conditions in order, like Location and non-blank total_vaccinations value.($A2=World!C:C)*(World!AI:AI<>""). If you needed, you could also add a date condition.
    – Emily
    Commented May 5, 2021 at 2:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .