VLookup (perhaps) - Returning first non-blank cell in row relative to reference

[husar]

Hello Community Members,

I have searched the forum for the answer to this conundrum, but cannot seem to find it. If you know the answer (or where I can find the answer) to the following scenario, then please post below.

Here is a simplified version of the excel spreadsheet that I am working with currently:
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]60[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]40[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD]60[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]70[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]35[/TD]
[TD]80[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

I would like to pull a value from "Column B" (or any column after ID) with a reference to the ID. As you can see, the ID numbers are on there multiple times, and sometimes the first corresponding variable cell in column B is blank. What type of formula can be created that will reference ID 1 (for example) and pull the first non-blank variable still attached to ID 1 (which would be "20" in this case). I do not want it to pull any variable not attached to the reference ID. For example, the first cell for ID 3 in column B is blank, but the next ID is 4 and thus the cell with "70" should not be pulled b/c it is not associated with ID 3.

Hopefully this makes sense. I can clarify if not. Any feedback/help would be appreciated! Thank you!

 

[anglais428]

Can you post what way you want the table to look (i.e. post formula)?

 

[Eric W]

Something like this might work:

	A	B	C	D	E	F	G	H	I

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]ID[/TD]
[TD="bgcolor: #FAFAFA"]Column A[/TD]
[TD="bgcolor: #FAFAFA"]Column B[/TD]
[TD="bgcolor: #FAFAFA"]Column C[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ID[/TD]
[TD="bgcolor: #FAFAFA"]Column A[/TD]
[TD="bgcolor: #FAFAFA"]Column B[/TD]
[TD="bgcolor: #FAFAFA"]ColumnC[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]60[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]11[/TD]
[TD="bgcolor: #FAFAFA"]No Match[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]30[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]40[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]50[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]60[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]70[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]35[/TD]
[TD="bgcolor: #FAFAFA, align: right"]80[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]{=IFERROR(INDEX(B:B,MIN(IF(($A2:$A9=$F$2)*(B2:B9<>""),ROW(A2:A9)),9^9)),"No Match")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in G2, and confirm with Control+Shift+Enter. Then copy that cell to H2 and I2. Let me know if this is the type of thing you're looking for.

 

[husar]

Hi Eric,

Thanks for the reply! The formula worked great for the first row. However, if I add other IDs in the following rows and copy the formula down (instead of across), the resulting numbers are incorrect.
In other words, if I add in the other IDs into column F and drag down the formula, it looks like this:

F G H I
3 11 nomatch 7
1 11 nomatch 7
2 11 nomatch 7
4 11

Putting "$" around some of the variables will probably fix this, so I was going to play around with it. While I do, any suggestions?

Thank you!

 

[Eric W]

Just remove the $ in red:

=IFERROR(INDEX(B:B,MIN(IF(($A2:$A9=$F$2)*(B2:B9<>""),ROW(A2:A9)),9^9)),"No Match")

 

[husar]

Just remove the $ in red:

=IFERROR(INDEX(B:B,MIN(IF(($A2:$A9=$F$2)*(B2:B9<>""),ROW(A2:A9)),9^9)),"No Match")

Amazing! Thank you so much It all works perfectly now.

 

[Eric W]

You're welcome!