Checking if a value exist in a table

Question

I have a test workbook with 2 worksheets. Sheet1 contains a gantt chart. Sheet2 contains a table of days lost.

I would like to check if a date exists in a table. If so, I would like to return a value of "L". If not, I would like to count the networkdays between the start date and the date on the calendar. However, I only want the formula to search for the date in the table column that corresponds to the value in Sheet1!A:A.

I have tried using index/match/match and got an error. I have tried just using match and got an error. I have tried using vlookup, but only the first column of the table was searched. I have tried using not/isna and got an error. I am lost and the solution is probably simple. I think I have been working on this thing for so long that I now have tunnel vision and can't think of anything else to try.

Keep in mind, this is a test workbook. The main workbook contains several pages of data and spans 2 years. The value in Sheet1A:A is dynamic and based on user selections. The days lost must be in a table because an absolute range is not practical.

Link to the test workbook: https://docs.google.com/spreadsheets/d/1ahbplaeqTJmSqYUEmDVOrREzro3MT-mz/edit?usp=sharing&ouid=109056396401531404556&rtpof=true&sd=true

Answer 1

Hi,

Hope, the following solution is helpful.

Formula: =IF(COUNT(MATCH(D$3,INDEX(Days_Lost,,MATCH($A4,Days_Lost[#Headers],0)),0))>0,"L",NETWORKDAYS($B4,D$3))

Please drag the formula down & right for other cells.

Note: In the above formula, if required, please edit cell references/ranges/worksheet name to suit Your requirement.

Please respond if You require further support. I will try My Best to be of help.

If I was able to help You, please mark My response as "answer" and "helpful".

Thank You!