Forum Discussion
Excel Lookup That Skips Cells
I'm afraid I can only half-follow what that formula is doing. Could you explain the logic?
Many thanks.
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.
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?
Sorry, I misunderstood the logic. Close to what HansVogelaar 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" ) ) )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
- HansVogelaar
So, if I'm understanding this correctly, the inner MATCH function finds the position of the value from A in the D column, then the INDIRECT allows concatenation of that into the definition of the lookup range into the C column, and then the XMATCH function searches bottom-to-top in the C column range that's just been defined to find the correct Ref?
That's an excellent solution, thank you. I was convinced I was going to need nested IF() statements to handle the conditionals.
