Forum Discussion
Excel Lookup That Skips Cells
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?
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.Yes, you have understood the formula correctly!