Need help with applying a formula to this scenario

Hello Gurus,

I really need help with formulating a formula for this scenario. I want to index and match column "Job #" from TABLE 1 to TABLE 2 and return the Status Date; however, the tricky part is the Job # may have multiple dates. In order to capture the correct date, I also want to match the first two letters of "Job Status" column from TABLE 1 to TABLE 2 and return the correct status date on TABLE 2.

EX. The first job # on the list is 277518 and has the Job status AB, the result i want would be TABLE 2 AB_Date 3/23/2023.

I have attached the spreadsheet as an example.

Thank you for helping.

C2=IF(FILTER(INDEX(Table13[[AB_DATE]:[MG_DATE]],,MATCH(LEFT(B2,2),SUBSTITUTE(Table13[[#Headers],[AB_DATE]:[MG_DATE]],"_DATE",""),0)),(Table13[Job '#]=A2)*(Table13[Job Status]=B2))>0,FILTER(INDEX(Table13[[AB_DATE]:[MG_DATE]],,MATCH(LEFT(B2,2),SUBSTITUTE(Table13[[#Headers],[AB_DATE]:[MG_DATE]],"_DATE",""),0)),(Table13[Job '#]=A2)*(Table13[Job Status]=B2)),"")

Try this in C2:

=LET(a,INDEX(FILTER(Table13,Table13[Job '#]=[@[Job '#]]),,MATCH(LEFT([@[Job Status]],2),LEFT(Table13[#Headers],2),0)),IF(a=0,"",a))

I tried it on my file and it doesn't work; however, it does with the sample I provided. It must have been some errors on my file. I attached another file with more accurate data that I hope will provide better samplings. Please take a look at the attached files with my notes.

thank you soo much!!

Originally Posted by Gregb11

Try this in C2:

=LET(a,INDEX(FILTER(Table13,Table13[Job '#]=[@[Job '#]]),,MATCH(LEFT([@[Job Status]],2),LEFT(Table13[#Headers],2),0)),IF(a=0,"",a))

Please check out my new attachment for better samplings. Thank you soo much for the help.

Sheet1

E4=FILTER(IFERROR(FILTER(INDEX(Table4[[IP_DT]:[RE_DT]],,MATCH(LEFT([@[Job Status]],2),LEFT(Table4[[#Headers],[IP_DT]:[RE_DT]],2),0)),(Table4[Job]=[@Job])),""),IFERROR(FILTER(INDEX(Table4[[IP_DT]:[RE_DT]],,MATCH(LEFT([@[Job Status]],2),LEFT(Table4[[#Headers],[IP_DT]:[RE_DT]],2),0)),(Table4[Job]=[@Job])),"")>0,"")

Copy down

This is really no different than my first post, just added the "IFERROR" to show blanks when needed, and just changed the table names. This seems to give all the right answers.


=IFERROR(LET(a,INDEX(FILTER(Table4,Table4[Job]=[@Job]),,MATCH(LEFT([@[Job Status]],2),LEFT(Table4[#Headers],2),0)),IF(a=0,"",a)),"")

BTW, @CARACALLA and my formula bring back the same answers with about 5 exceptions. I'm not sure why they are different, but one of us is missing something. The exceptions are rows where I show a date and CARACALLA does not. Row 116 is an example of one of them. Should the result be blank or 6/22/23? I may have to tweak my formula if it's supposed to be blank.

hi everyone, after check the consult I allow me to propose this alternative possible solution:

Please Login or Register  to view this content.

I hope it results useful.

All these formulas work when i enter into the sample file I've provided; however, when I enter on my actual spreadsheet, it doesn't work. I even changed the column and table name to match the sample but it's still not working. The only difference between the two files is the number of columns. The sample file has less compared to the actual file, but the associated columns in the example file are all there in the actual file so I am not sure where the issue lies. Any way to troubleshoot it?

Originally Posted by Gregb11

BTW, @CARACALLA and my formula bring back the same answers with about 5 exceptions. I'm not sure why they are different, but one of us is missing something. The exceptions are rows where I show a date and CARACALLA does not. Row 116 is an example of one of them. Should the result be blank or 6/22/23? I may have to tweak my formula if it's supposed to be blank.

Yours would be the formula I would want due to the including of those 5 jobs. I believe the difference comes from @caracalla exclusion of the IN_DT in her formula [[IP_DT]:[RE_DT]].

It's hard to troubleshoot without seeing anything. When you say "it doesn't work", what does that mean? Are you getting Dates you're not expecting? Are you getting dates where you don't expect a date? If you can attach your actual file (or a subset of it where it still doesn't work).

Originally Posted by Gregb11

It's hard to troubleshoot without seeing anything. When you say "it doesn't work", what does that mean? Are you getting Dates you're not expecting? Are you getting dates where you don't expect a date? If you can attach your actual file (or a subset of it where it still doesn't work).

with your formula, I get random data, some with correct data (date from the correct column), while others are getting data from different columns (not date) but its always from the same wrong column if that helps. Does it matter where the "Job" column is on the Table3 and Table4 lookup table? My example file it is located in Column A, but in the actual file it is in Column E. The location of the columns are different between the two files but the header name are the same. Unfortunately, i cant upload the actual files due to company policy.

Ah, yes, if your headers begin with the same 2 digits as the job status, it will get mixed up. In your lookup table, if you always have the job status followed by an underscore, try this instead:
=IFERROR(LET(a,INDEX(FILTER(Table4,Table4[Job]=[@Job]),,MATCH(LEFT([@[Job Status]],2)&"_",LEFT(Table4[#Headers],3),0)),IF(a=0,"",a)),"")

Originally Posted by Gregb11

Ah, yes, if your headers begin with the same 2 digits as the job status, it will get mixed up. In your lookup table, if you always have the job status followed by an underscore, try this instead:
=IFERROR(LET(a,INDEX(FILTER(Table4,Table4[Job]=[@Job]),,MATCH(LEFT([@[Job Status]],2)&"_",LEFT(Table4[#Headers],3),0)),IF(a=0,"",a)),"")

This is it! It works! THANK YOU SO MUCH!!

You're welcome, and thanks for the rep.