Hi, referring to the attached excel file & screenshot,

quick to the question:

For cell G3,


i need to lookup the value of cell B3 (which is "0019" on Day 4229), from B4 to F12. (till the bottom of the table)
As "0019" found on cell C8, which is Day 4224, the output on cell G3 will be 4229 minus 4224, which is "5"

example 2,
For cell H3,
i need to lookup the value of cell C3 (which is "1124" on Day 4229), from B4 to F12 (till the bottom of the table).
As "1124" found on cell E12, which is Day 4220, the output on cell H3 will be 4229 minus 4220, which is "9"


For cell G4,
i need to lookup the value of cell B4 (which is "0905" on Day 4228), from B5 to F12. (till the bottom of the table)
As "0905" found on cell F6, which is Day 4226, the output on cell G4 will be 4228 minus 4226, which is "2"

what would be the best formula to get this done?
i've tried vlookup , index match , but could't figure out how to get it done.

Attached Images

1.JPG (59.1 KB, 12 views)

Attached Files

Find Days.xlsx (9.3 KB, 8 views)

Hi,

In G3, try:

=$A3-IFERROR(INDEX($A4:$A$12,SUMPRODUCT(($B4:$F$12=B3)* (ROW($B4:$F$12)-ROW($B4)+1))),0)

copied down and across.

If there is no match and you want blanks instead of the large numbers, then use

=IFERROR($A3-INDEX($A4:$A$12,SUMPRODUCT(($B4:$F$12=B3)*(ROW($B4 :$F$12)-ROW($B4)+1))),"")