Excel Formula to Look up a category, then see if it has a certain subcategory and then spit out value

[5472dch]

I have a spreadsheet that is formatted a way to make simpler functions not possible (as far as I know). I don't want to have to reformat it nor do I want to use macros. The ranges are dynamic so I am unable to just hardcode the locations of the information I want. My plan/hope here is to have the data be imported and autofill tables. I included a screenshot of the weird formatting.

For example I want to use a formula to find the value in Column C for Job6 for Name1.

Thank you for your time!

 

[hagia_sofia]

Please test this (lookup values are hardcoded based on your example but you can replace them with cell references based on your needs):

=XLOOKUP("Name1"&"Job6",SCAN("",A1:A23,LAMBDA(a,b,IF(b<>"",b,a)))&B1:B23,C1:C23)

 

[5472dch]

Thank you! this seems to work!