Lookup Issues

[djjamesp]

So, quite a complex one that's consumed my life for a week and I still cannot get it working..

I have a spreadsheet which is the data from audits completed, it's a substantial size (goes up to column KP). Each row has a unique reference / identifier number. Each question has a possible outcome of yes, no, N/A or a failure code.

The failure codes are contained in another table - these codes could appear in virtually any one of the cells from "R" to "KP", there are 71 codes.

What I'm trying to achieve is to build a kind of dashboard, where the unique reference number would list the failure codes and if the codes are not found (as there are no failures) it would say Pass.

Is this possible to have a lookup of 71 codes? I can't seem to get xlookup to work over such a range, am I doing anything wrong?

 

[StephenCrump]

Just a guess to get started (it would help if you showed a sample layout and your desired results) ...

	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	FailCodes							ID	Q1		Q2			Q3	Q4		Q5		Q6	etc etc
2	A	Data missing						111	Yes		Yes			No	No		Yes		n/a
3	B	Invalid data field						222	No		No			n/a	C		D		Yes
4	C	Too big						333	Yes		Yes			No	n/a		Yes		No
5	D	Out of stock						444	Yes		B			C	E		No		n/a
6	E	Too soft
7
8	Results
9	222	Too big, Out of stock
10
Sheet1

Cell Formulas
Range	Formula
K9	K9	=LET(t,TEXTJOIN(", ",,XLOOKUP(XLOOKUP(J9,Q$2:Q$5,R$2:AC$5),J$2:J$6,K$2:K$6,"")),IF(LEN(t),t,"Pass"))

 

[djjamesp]

Thanks for the reply - I will have a play with the syntax in that formula.

I've attached a (very scaled down) version of what I'm trying to achieve, it's difficult as I cannot post the originals as they contain sensitive data. Each of the purple headings is a separate sheet within the workbook, I hope this makes sense

Thanks in advance

James

 

[StephenCrump]

The best way to post a screenshot is using the XL2BB add-in. Then we don't need to re-create your layout from a picture.

In which columns will the error codes appear? You originally said:

these codes could appear in virtually any one of the cells from "R" to "KP"

Instead, can we rely on them appearing in every second column P, R, T ... as shown in Post#3?

Is the lookup table with the values relevant to the question?

And how many rows of data will there be? You say you have >200 columns, so a formula approach may be computation heavy if there are too many rows.