If statement based on xlookup or some lookup that return values from a column based on header values

[sirmixalot]

Hi guys,

As part of my world domination scheme, I am trying to figure out how to return a value from the first table rendering below (row values) based on the column headers in the same table. I use the Loc1/Loc2/Loc3 values in column 1 of table 2 to return a "yes" or "no" from table 1.

I tried @XLookup and I feel like that returns the right answer but for some reason, it does not fit well within an "IF" statement and always gives me false even though I mark it as "If(@XLOOKUP(formula here with exact match))="Yes",1,2) - it always returns 2

Loc1	Loc2	Loc3	Loc4	Loc5
Yes	Yes	Yes	Yes	No
Yes	Yes	Yes	No	No
Yes	Yes	Yes	No	No
Yes	Yes	Yes	Yes	No
Yes	Yes	Yes	Yes	No
Yes	Yes	Yes	Yes	No
Yes	Yes	Yes	Yes	Yes
Yes	Yes	Yes	Yes	Yes
Yes	Yes	Yes	Yes	Yes

location	plan	plan[id]
Loc1	bm	LOc1-bm
Loc2	bm	Loc2-bm
Loc3	bm	Loc3-bm
Loc4	bm	Loc4-bm
Loc5	bm	Loc5-bm

 

[CA_Punit]

What are you trying to match. Please provide a sample data instead of result so that we can help.

And where is 2..

it always returns 2

Please help me with more clarification. Thanks

 

[sirmixalot]

Thanks for the comment @CA_Punit !

I redid the exercise as follows in a blank sheet and got the answer i was looking for.

For Context,

Sheet 1 is the first table as shown and Sheet 2 is the second table. Here is what I try

if I try the following:
=(@XLOOKUP(A19,Sheet1!$O$5:$S$5,Sheet1!$O$6:$S$26,"not found",0)), I get "Yes"

However, if I try the following:

=@IF(XLOOKUP(A19,Sheet1!$O$5:$S$5,Sheet1!$O$6:$S$26,"not found",0)="Yes",1,2)

I was getting 2 in the sheet I was working in but in a blank sheet, I am getting 1 which informs me there was something wrong with my sheet.

 

[Fluff]

Not sure what you are trying to do, or if you realise this, but your formula is effectively only looking at row 6 ie
=IF(XLOOKUP(A19,Sheet1!$O$5:$S$5,Sheet1!$O$6:$S$6,"not found",0)="Yes",1,2)