Variable row, variable column and retrieve text

Question

I'm using Microsoft 365.

Working with a set up like this, my goal is when someone specifies "Sandwich", having a formula that will return a list of ingredients that are needed. So the formula would need to find the correct row, search the row for "R" or "O", and then if found, go to the top row (which is a constant) and return the name of that ingredient.

I've tried using combinations of INDEX, MATCH, XLOOKUP, VLOOKUP and a couple other things, but I'm stumped.

I'll also want a second column next to the compiled list of ingredients that reflects if it's Required or Optional, but thinking I'll be able to amend the first equation to accomplish that.

	Ham. Bun	Hotdog Bun	Bread	Ketchup	Mustard	Mayo	Onions
Hot Dogs		R		O	O		O
Hamburgers	R			O	O	O	O
Sandwich			R	O	O	O	O
Bratwurst		O		O	O

Answer 1

Hi,

In cell K2, enter this formula

=TOCOL(FILTER($B$1:$H$1,XLOOKUP(J2,$A$2:$A$5,$B$2:$H$5)<>""))

In cell L2, enter this formula

=LET(rw,XLOOKUP(J2,$A$2:$A$5,$B$2:$H$5),TOCOL(FILTER(rw,rw<>"")))

Hope this helps.

Answer 2

Hi,

In cell K2, enter this formula

=ARRAYTOTEXT(FILTER($B$1:$H$1,XLOOKUP(J2,$A$2:$A$5,$B$2:$H$5)<>""))

In cell L2, enter this formula

=LET(rw,XLOOKUP(J2,$A$2:$A$5,$B$2:$H$5),ARRAYTOTEXT(FILTER(rw,rw<>"")))

Hope this helps.

Answer 3

Thank you for your help!

I'm still also trying to figure it out myself, but is there a way to make the results show up in a list instead? My ultimate goal is for the table pulling from the dataset to look like the following - so then the user can manually check off when an ingredient is obtained, in the example below:

I have no experience with the newer equations that allow for information to cascade out of a cell, so I'm trying to play around with those utilizing what you've shared above.

Answer 4

Let's say the data are in A1:H5 on a sheet named Ingredient List.

On another sheet, you enter Sandwich in A2.

In B2:

=LET(f, FILTER('Ingredient List'!$B$2:$H$5, 'Ingredient List'!$A$2:$A$5=A2), r, FILTER('Ingredient List'!$B$1:$H$1, f="R", ""), o, FILTER('Ingredient List'!$B$1:$H$1, f="O", ""), "R: "&TEXTJOIN(" R: ", TRUE, r)&" O: "&TEXTJOIN(" O: ", TRUE, o))