Excel Lookup That Skips Cells

Question

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.&nbsp;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.&nbsp;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.&nbsp;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.&nbsp;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.&nbsp;&nbsp;&nbsp;

sergeibaklan · Answer

Morbeus&nbsp;
As variant
=LET(f, FILTER(Sheet2!C:C, (Sheet2!D:D=A1) ), IFERROR( @FILTER( f, f&lt;&gt;0 ), "no such") )

morbeus · Answer

SergeiBaklanI'm afraid I can only half-follow what that formula is doing. Could you explain the logic?Many thanks.

sergeibaklan · Answer

Morbeus&nbsp;
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.

morbeus · Answer

SergeiBaklan&nbsp;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.&nbsp;The columns look something like this on Sheet1:&nbsp; &nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B&nbsp; &nbsp; &nbsp;Text.1&nbsp; &nbsp; &nbsp;FormulaText.2&nbsp;&nbsp; &nbsp; &nbsp;FormulaText.3&nbsp; &nbsp;&nbsp; &nbsp;FormulaText.4&nbsp; &nbsp; &nbsp;&nbsp;FormulaText.5&nbsp; &nbsp; &nbsp;&nbsp;FormulaText.6&nbsp; &nbsp; &nbsp;&nbsp;Formula&nbsp;On Sheet2:&nbsp; C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DRef.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Text.10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Text.20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0Ref.2&nbsp; &nbsp; &nbsp;Text.3Ref.3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Text.4Ref.4&nbsp; &nbsp; &nbsp;Text.5&nbsp;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.&nbsp;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.&nbsp;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.&nbsp;Does that make the problem a little more clear?

hansvogelaar · Answer

Morbeus&nbsp;
Does this do what you want?
=INDEX(Sheet2!C:C,XMATCH(TRUE,INDIRECT("Sheet2!C1:C"&amp;MATCH(A1,Sheet2!D:D,0))&lt;&gt;0,0,-1))

You can wrap the INDEX formula in IFERROR to return something else instead of #N/A, of course

Forum Discussion

Excel Lookup That Skips Cells

8 Replies

Resources