Nested XLOOKUP help

In need of XLOOKUP help!

Attached is the Sheet VENDOR NAME

I am struggling to write an XLOOKUP formula that will give the desired outcome in Truck Template G2 through G4. The formula will live in column G of the Truck Template tab. I need it to take the Material Code in column A and compare it to Code column (column E on the Materials tab). I need it to find the correct line in the Materials tab and return the vendor name (Materials V1 through X1) with the value of 1 in V2 through X4.

I tried to use =XLOOKUP(A2,Materials!E2:E4,(XLOOKUP(Z1,Materials!V1:X1,Materials!V2:X4)))

Z1 is a reference cell that contains the number 1.

Any help would be greatly appreciated. Please and thank you!

Regards,
David

It's just meant to be reference. It would contain the number 1.

Try this in G2 and copy down:

=INDEX(Materials!$V$1:$X$1,,MATCH(1,FILTER(Materials!$V$2:$X$4,Materials!$E$2:$E$4=A2),0))

Another option:

=XLOOKUP(1,FILTER(Materials!$V$2:$X$4,Materials!$E$2:$E$4=A2),Materials!$V$1:$X$1,"",0)

Another formulas

Formula:

Please Login or Register  to view this content.

Named tables.

Formula:

Please Login or Register  to view this content.

Both formulas worked like a champ, Greg! Thank you very much for the quick reply!

This also works DJ! Thank you for your help!

I already took care of that. Thank you!

You're welcome, and thanks for the rep and feedback.