Sep 25 2023 08:41 AM
Hi all,
Been struggling with this problem that seems like it should be simple, but I suspect I don't know enough to figure out what combination of things would work.
To summarise the setup (simplified) I have two sheets, Sheet 1 has some data in Column A, and I want to put the lookup formula in Column B.
Sheet 2 has data in Column C and D, I want the formula to look up the value of the Sheet 1 A cell in Column D, then return the corresponding value in Column C. However, if the value is 0, I want the formula to look "up" to find the next non-0 cell and return that value.
So, the first bit is relatively simple, I can just do =XLOOKUP(A1, Sheet2!D:D, Sheet2!,C:C) and that takes care of looking up to the left. What I'm having trouble with is how to then go up to the next non-zero cell.
I've got a workaround for the moment where I use =IF(XLOOKUP(A2, Sheet2!D:D, Sheet2!,C:C)=0,B1,XLOOKUP(A2, Sheet2!D:D, Sheet2!,C:C))
Basically, if the lookup isn't 0 use that value, if it is then use the value in the cell above this one. This isn't ideal as it needs manual verification that it's correct, and relies on there being something in the cell above that makes sense.
So, I'm fairly sure I need to modify this IF() statement to do something more interesting when the lookup returns 0, but I've not been able to figure out what. If anyone has any thoughts, I'd be extremely grateful.
Sep 25 2023 08:56 AM
As variant
=LET(f, FILTER(Sheet2!C:C, (Sheet2!D:D=A1) ), IFERROR( @FILTER( f, f<>0 ), "no such") )
Sep 25 2023 09:15 AM
Sep 25 2023 09:24 AM
We filter from column C in Sheet2 all values for which value in column D is equal to A1. Result is filtered on all non-zero values. From these non-zero values we take first found by applying "@". Finally, if nothing was found, IFERROR gives a message.
Sep 26 2023 04:32 AM
Thank you for the explanation.
Unfortunately, this doesn't seem to be working, in that it's returning the "no such" message. I may not have explained properly.
The columns look something like this on Sheet1:
A B
Text.1 Formula
Text.2 Formula
Text.3 Formula
Text.4 Formula
Text.5 Formula
Text.6 Formula
On Sheet2:
C D
Ref.1 0
0 Text.1
0 Text.2
0 0
Ref.2 Text.3
Ref.3 0
0 Text.4
Ref.4 Text.5
In reality, there are other columns on the sheets between these with other data, but this is the setup for the ones I care about at the moment.
The idea is, look in each Sheet1 A cell, find where the value there is in the Sheet2 D column, then look to the left and return the value from the C column. If there's nothing there, then look "up" the column for the next non-zero entry and return that.
So, the formula in B1 and B2 looks at the values for A1 and A2 respectively and both should return "Ref.1", the formula in B3 should return "Ref.2", and so on.
When I apply the formula you kindly suggested to this example, I get the "no such" message on rows 1, 2, 4, and 6, i.e., wherever the Ref. is not immediately to the left of the Text.
Does that make the problem a little more clear?
Sep 26 2023 04:46 AM
Does this do what you want?
=INDEX(Sheet2!C:C,XMATCH(TRUE,INDIRECT("Sheet2!C1:C"&MATCH(A1,Sheet2!D:D,0))<>0,0,-1))
You can wrap the INDEX formula in IFERROR to return something else instead of #N/A, of course
Sep 26 2023 06:17 AM
Sep 26 2023 07:40 AM
Sorry, I misunderstood the logic. Close to what @Hans Vogelaar suggested
=LET(
ref, Sheet2!C:C,
txt, Sheet2!D:D,
IF(A1="", "",
IFNA(INDEX( ref, XMATCH( XMATCH(A1,txt,0),ROW(ref)*(ref<>0),-1) ), "no such" ) ) )
Sep 26 2023 08:00 AM
Yes, you have understood the formula correctly!