Forum Discussion
Excel Lookup That Skips Cells
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 (simp...
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.
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!