Lookup & return formula

[ArPharazon]

I hope I can explain this well. In a nutshell, what I'm trying to do here is: Use D1 #1 & B4 #2 on the first sheet (first image) as references to see if #2 exists in the second sheet (second image), in the relevant row (Row 6 in this case, up to 167 columns I6:FW6) and return an "X" to D4 #3

There are many more blank columns on the first sheet and I'm certain I'll have to copy the formula over to each column, reset the values, etc).

@JamesCanale provided a great formula for finding a specific string within a range of horizontal cells with this formula, and I feel like something similar may be needed, but probably needs to be more complex.

Range	Formula
Cell Formulas
M2:M10	M2	=IF(LEFT(INDEX(B2:K2,1,COUNTA(B2:K2)),7)="Network",INDEX(B2:K2,1,COUNTA(B2:K2)),"not found")

 

[GlennUK]

Is that matching for finding out row in second sheet always going to be testing the contents of column C? What is the name of the second sheet? It sounds like a couple of INDEX functions with MATCH, and using COUNTIF to count, should do it ... like: =IF(COUNTIF(INDEX(Sheet2!$I$1:$I$999,MATCH(D$1,Sheet2!$C$1:$C$999,0)):INDEX(Sheet2!$FW$1:$FW$999,MATCH(D$1,Sheet2!$C$1:$C$999,0)),$B4)>0,"X","")

 

[ArPharazon]

Is that matching for finding out row in second sheet always going to be testing the contents of column C? What is the name of the second sheet? It sounds like a couple of INDEX functions with MATCH, and using COUNTIF to count, should do it ... like: =IF(COUNTIF(INDEX(Sheet2!$I$1:$I$999,MATCH(D$1,Sheet2!$C$1:$C$999,0)):INDEX(Sheet2!$FW$1:$FW$999,MATCH(D$1,Sheet2!$C$1:$C$999,0)),$B4)>0,"X","")

In this case, yes, column C in the second sheet is the match for D1 on the initial sheet.

Changing Sheet2 to 'Roles_8-8-23' seems to be returning the same X's as I got from the more manual approach, thanks so much for the help!

 

[GlennUK]

It sounds like all is working as expected. Great ! ! !