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:

ID	Column A	Column B	Column C
1	60		10
1	30	20
1	0	40	9
2		50	8
2	10	60	8
3	11		7
4		70	6
4	35	80	5

<tbody>
</tbody>

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
1	ID	Column A	Column B	Column C		ID	Column A	Column B	ColumnC
2	1	60		10		3	11	No Match	7
3	1	30	20
4	1	0	40	9
5	2		50	8
6	2	10	60	8
7	3	11		7
8	4		70	6
9	4	35	80	5
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Array Formulas Cell Formula G2 {=IFERROR(INDEX(B:B,MIN(IF(($A2:$A9=$F$2)*(B2:B9<>""),ROW(A2:A9)),9^9)),"No Match")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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!