#1
|
|||
|
|||
Need help with lookup formula
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. |
#2
|
||||
|
||||
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))),"") |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula help please (lookup across multiple sheets) | froggybsb03 | Excel | 2 | 03-16-2017 02:16 AM |
"lookup" formula | MrT2016 | Excel | 2 | 12-20-2016 07:40 PM |
LOOKUP - Complex lookup with 2 lookups in 1 cell | sglandon | Excel | 6 | 05-05-2016 09:44 AM |
Lookup | Tony Singh | Excel | 3 | 03-06-2015 11:03 AM |
Lookup | angie.chang | Excel | 1 | 07-27-2012 09:45 PM |