• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reverse lookup based on a data value?

Hi all,
I know I should know this one but having something of a challenge here.



Sample attached. Basically I want to pull all of the data highlighted in yellow, into the cells in green. I cannot do anything about the format in yellow.

The Project is determined by the dropdown in red. This is where I am running into a road block. Pulling the first few cells with the right data is ok, but how do I then find the first month, which is determined by it being the first month with any value other than 0 and the subsequent data?

Any support much appreciated.

Thanks,

-CL
 

Attachments

  • Sample.xlsx
    14.4 KB · Views: 7
Assuming you have 365, try this:

Code:
=LET(d,D5:V24,p,FILTER(A5:C24,A5:A24=X2),
fm,BYCOL(d,SUM),
x,XLOOKUP(99999999,fm,D3:V3,"",-1),
HSTACK(p,FILTER(FILTER(d,D3:V3>=x),A5:A24=X2)))
 

Attachments

  • Cantonalives FILTER Chandoo SPILL 365 AliGW.xlsx
    14.6 KB · Views: 2
Assuming you have 365, try this:

Code:
=LET(d,D5:V24,p,FILTER(A5:C24,A5:A24=X2),
fm,BYCOL(d,SUM),
x,XLOOKUP(99999999,fm,D3:V3,"",-1),
HSTACK(p,FILTER(FILTER(d,D3:V3>=x),A5:A24=X2)))
Hi, thanks for that.

Worked well to a point. The problem I was having is how to pull the month in too. So what you have achieved, but also bringing the month in to it. Any guidance much appreciated..


Thanks,

-CL
 
It does exactly what you asked for in your sample data. Did you actually try it or just look at it and not see MONTH and assume it wasn't right?

This bit finds the first column with an entry:

Code:
x,XLOOKUP(99999999,fm,D3:V3,"",-1),

If it's not doing what you want, then you need to provide a sample data set that shows it not working and also contains an example of what you want instead.
 
Last edited:
It does exactly what you asked for in your sample data. Did you actually try it or just look at it and not see MONTH and assume it wasn't right?

This bit finds the first column with an entry:

Code:
x,XLOOKUP(99999999,fm,D3:V3,"",-1),

If it's not doing what you want, then you need to provide a sample data set that shows it not working and also contains an example of what you want instead.


Thank you again. As per my original post, I am trying to capture all of the data in yellow, which includes the headers.


Your formula works really well for the data aspect, but I need the header to come through too. So the first column with any data that is not zero, but need to know which month that is. Reattached with your formula in Orange. It is the blue I am missing..


Thank you again..

-CL
 

Attachments

  • Sample.xlsx
    15.1 KB · Views: 1
This, then:

Code:
=LET(d,D5:V24,p,FILTER(A5:C24,A5:A24=X2),
fm,BYCOL(d,SUM),
x,XLOOKUP(99999999,fm,D3:V3,"",-1),
HSTACK(VSTACK(A4:C4,p),VSTACK(FILTER(D3:V3,D3:V3>=x),FILTER(FILTER(d,D3:V3>=x),A5:A24=X2))))
 

Attachments

  • Cantonalives FILTER Chandoo SPILL 365 AliGW.xlsx
    15.2 KB · Views: 1
This, then:

Code:
=LET(d,D5:V24,p,FILTER(A5:C24,A5:A24=X2),
fm,BYCOL(d,SUM),
x,XLOOKUP(99999999,fm,D3:V3,"",-1),
HSTACK(VSTACK(A4:C4,p),VSTACK(FILTER(D3:V3,D3:V3>=x),FILTER(FILTER(d,D3:V3>=x),A5:A24=X2))))
Actually, one issue if it is not me. When I change the dropdown in x2, it does not dynamically find the first data and month anymore? It is still on the first month from Project 1.
 
Sorry - try this:

Code:
=LET(a,A5:A24,b,X2,d,FILTER(D5:V24,a=b),p,FILTER(A5:C24,a=b),
fm,BYCOL(d,SUM),
x,XLOOKUP(99999999,fm,D3:V3,"",-1),
HSTACK(VSTACK(A4:C4,p),VSTACK(FILTER(D3:V3,D3:V3>=x),FILTER(d,D3:V3>=x))))
 

Attachments

  • Cantonalives FILTER Chandoo SPILL 365 AliGW.xlsx
    15.3 KB · Views: 3
There's a breakdown in the attached showing what each parameter in the formula returns.
 

Attachments

  • Cantonalives FILTER Chandoo SPILL 365 AliGW.xlsx
    17 KB · Views: 2
You sent me a PM - I will not provide help via PM. Once you have asked the question here, I will help you.

Thanks.
 
Last edited:
Here's your PM:

Hi again and thank you again.

Hoping you will not mind me imposing again?

Attached I have tried to broaden the data out and failed, in that it repeats months for Project 14 and does not even detect the first month where there is something other than a zero.

CN17 is where the array is now located. CQ thru DK repeats 3 months.

Any guidance much appreciated.

Thanks,

-CL

It's user error. It fails becase the month headers in D2 onwards are TEXT. They need to be DATES, as they were in the sample workbook.

Once you fix this, it should work. Sorry - it's not for me to do this for you.

Next time ask here, NOT via private message, please.
 
Back
Top