Xlookup Starting Point

[OKCIrish]

Hello,

I'm new to excel, however I am very good at solving issues if I can get going in the right direction or have previous formulas to work off of or modify. I am having trouble on how to solve/approach this data point I would like returned to me.

My main data point that needs to be returned to a specific text in column X is OK, OK-HB, RPR or REJ is based on what is inputted in column K, M, N, T & V, from either drop-down menu selected or manually inputted.

I have been told a nested if formula would cause a lot of complications and Xlookup would be a better suit. However, I am stuck on how I should approach this table for Xlookup to work correctly. Do I have to have all different kinds of variations on the table, or can there be key components for the formulas to look up and return?

In column K & T if the drop down or manual input of STS or UND is selected then it overrides everything and the text in column X comes up as REJ.
Then column K, T, and V would compare for special text in column K & T and column V would have an X in it and column X would be returned as RPR if any of those parameters are met.
After that column K, T, M & N would be looked at. Column K & T would have to have either OK or OK-FR then column M & N would have F or D in it and then column X would return with OK-HB
And finally if column K, M, N, T & V have OK or OK-FR then column X would be returned as OK, with the variance of sometimes column N not having OK in the column at all but still returning column X with OK.

I would greatly appreciate any leads on how to approach this.

 

[awoohaw]

Can you use the xl2bb add in to paste a mini worksheet sample of your worksheet?
Or at minimum, paste an image that we can copy actual cells so we don't have to create the workbook from scratch?

 

[OKCIrish]

Can you use the xl2bb add in to paste a mini worksheet sample of your worksheet?
Or at minimum, paste an image that we can copy actual cells so we don't have to create the workbook from scratch?

DRILCO DP Report V4.4 - Copy.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X		Z
50							Box Connection						Hard Band		Pin Connection
51	Joint No.	Serial Number (1)	Serial Number (2)	Min Wall	RBW%	Flaking ID Ctg	Box TJ OD	Box Bevel Dia.	Box Length	Box Tong Space	Box Initial Cond.	__	Box HB	Pin HB	Pin TJ OD	Pin TJ ID	Pin Bevel Dia.	Pin Length	Pin Tong Space	Pin Initial Cond.	___	Bent?	Joint Classification	Final Class		Comments
52	1
53	2
54	3
55	4
56	5
57	6
Drill Pipe

Cell Formulas
Range	Formula
W52:W57	W52	=IF(E52="","",IF(E52<=80%,"2","P"))
X52:X57	X52	=IF(OR(K52="",T52=""),"",IF(AND(OR(K52="OK",K52="OK-FR"),OR(T52="OK",T52="OK-FR")),"OK","RPR"))
E52:E57	E52	= IF([@[Min Wall]]="","",[@[Min Wall]]/$R$36)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K52:K1551,T52:T1551	Cell Value	contains "CT"	text	NO
P52:P1551	Cell Value	>General!$O$16	text	NO
X52:X1551	Cell Value	beginning with "REJ"	text	NO
X52:X1551	Cell Value	ending with "HB"	text	NO
X52:X1551	Cell Value	beginning with "RPR"	text	NO
B52:B1551	Cell Value	duplicates	text	NO
E52:E1551	Cell Value	between 0.7 and 0.7999	text	NO
E52:E1551	Cell Value	between 0.6 and 0.6999	text	NO
E52:E1551	Cell Value	between 0.001 and 0.5999	text	NO
V52:V1551	Expression	=IF($V52="X",TRUE,FALSE)	text	NO
K52:K1551	Expression	=IF($K52="MRF",TRUE,FALSE)	text	NO
T52:T1551	Expression	=IF($T52="MRF",TRUE,FALSE)	text	NO
N52:N1551	Expression	=IF($N52="D",TRUE,FALSE)	text	NO
M52:M1551	Expression	=IF($M52="D",TRUE,FALSE)	text	NO
N52:N1551	Expression	=IF(OR($N52="F",$N52="W"),TRUE, FALSE)	text	NO
M52:M1551	Expression	=IF(OR($M52="F",$M52="W"),TRUE,FALSE)	text	NO
R52:R1551	Cell Value	between 0.0001 and $W$42 - 0.0001	text	NO
R52:R1551	Cell Value	>$W$43	text	NO
I52:I1551	Cell Value	between 0.0001 and $W$40 - 0.00001	text	NO
I52:I1551	Cell Value	>$W$41	text	NO
S52:S1551	Cell Value	between $R$44 and $R$44+0.75	text	NO
S52:S1551	Cell Value	between 0.001 and ($R$44-0.001)	text	NO
J52:J1551	Cell Value	between $R$43 and $R$43+0.75	text	NO
J52:J1551	Cell Value	between 0.001 and $R$43-0.001	text	NO
G52:G1551,O52:O1551	Cell Value	between $R$41 and $R$41+0.0625	text	NO
G52:G1551,O52:O1551	Cell Value	between 0.001 and $R$41-0.001	text	NO
E52:E1551,T52:T1551,M52:N1551,K52:K1551	Expression	=IF(OR(E52=$E$24:$E$25),TRUE,FALSE)	text	NO
E52:E1551,T52:T1551,M52:N1551,K52:K1551	Expression	=IF(OR(E52=$E$8:$E$23),TRUE,FALSE)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F52:F57	List	=Lists!$K$24:$K$28
T52:T57	List	=Lists!$J$3:$J$23
K52:K57	List	=Lists!$J$3:$J$23
V52:V1551	List	=Lists!$K$21
W52:W1551	List	=Lists!$K$14:$K$18
X52:X1551	List	=Lists!$K$2:$K$5
M52:N57	List	=Lists!$K$8:$K$10

 

[OKCIrish]

Can you use the xl2bb add in to paste a mini worksheet sample of your worksheet?
Or at minimum, paste an image that we can copy actual cells so we don't have to create the workbook from scratch?

Sorry about that, last time I couldn't get it to load. There is a small IF formula in column X already that I got help with and just expanded on.

 

[awoohaw]

Sorry about that, last time I couldn't get it to load.

Thanks, I'll take a look now.

 

[awoohaw]

Sorry about that, last time I couldn't get it to load. There is a small IF formula in column X already that I got help with and just expanded on.

I copied the worksheet, but there is no data.

 

[OKCIrish]

I copied the worksheet, but there is no data.

Does it matter if the drop down data is on another tab? Or do I need to prefill like how I have in my attachment and then re-paste the XL2BB?

 

[awoohaw]

I don't think the drop downs are that critical. But, the data is important for me to recreate your issues. YOu have a lot of dependencies. The cell formatting (including conditional formatting) did not copy either... but I think that is the nature of the xl2bb beast. The worksheet has table references. Can you go to your Excel Options and change table references to show the range values before you copy? I don't see a table or know where to look for one. In the one formula that did copy over you refer to a vaue in row36 I think that is not in the mini workbook. Maybe you can copy Row 1 to row 50? And be sure that includes all/most the possible scenarios of data permutation as well as the input columns used in calculations.?

But, I guess .. what I really want to know based on the title of the post... is what is it about XLOOKUP you want to figure out?

 

[OKCIrish]

I don't think the drop downs are that critical. But, the data is important for me to recreate your issues. YOu have a lot of dependencies. The cell formatting (including conditional formatting) did not copy either... but I think that is the nature of the xl2bb beast. The worksheet has table references. Can you go to your Excel Options and change table references to show the range values before you copy? I don't see a table or know where to look for one. In the one formula that did copy over you refer to a vaue in row36 I think that is not in the mini workbook. Maybe you can copy Row 1 to row 50? And be sure that includes all/most the possible scenarios of data permutation as well as the input columns used in calculations.?

But, I guess .. what I really want to know based on the title of the post... is what is it about XLOOKUP you want to figure out?

The first row on the worksheet is just a tabulation for all the data that is being inputted into the worksheet. I wasn't able to figure out on how to add the range values in before I copied over the mini sheet in this go around as well.

I guess I wanted to see if XLOOKUP would be able to help me solve this issue. I have brought this up to someone before and they mentioned putting the combinations in one table and just have XLOOKUP reference that table to fill in what I need on the main table.

 

[OKCIrish]

DRILCO DP Report V4.4 - Copy.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X		Z
50							Box Connection						Hard Band		Pin Connection
51	Joint No.	Serial Number (1)	Serial Number (2)	Min Wall	RBW%	Flaking ID Ctg	Box TJ OD	Box Bevel Dia.	Box Length	Box Tong Space	Box Initial Cond.	__	Box HB	Pin HB	Pin TJ OD	Pin TJ ID	Pin Bevel Dia.	Pin Length	Pin Tong Space	Pin Initial Cond.	___	Bent?	Joint Classification	Final Class		Comments
52	1										STS		OK	OK						OK				Rej
53	2										UND		OK	OK						OK				Rej
54	3										OK		OK	OK						STS				Rej
55	4										OK		OK	OK						UND				Rej
56	5										OK		OK	OK						OK		X		RPR
57	6										MRF		OK	OK						OK				RPR
58	7										PIT		OK	OK						OK				RPR
59	8										WT		OK	OK						OK				RPR
60	9										CT		OK	OK						OK				RPR
61	10										GT		OK	OK						OK				RPR
62	11										WO		OK	OK						OK				RPR
63	12										BT		OK	OK						OK				RPR
64	13										BV		OK	OK						OK				RPR
65	14										BM		OK	OK						OK				RPR
66	15										DIM		OK	OK						OK				RPR
67	16										PUT		OK	OK						OK				RPR
68	17										CS		OK	OK						OK				RPR
69	18										GS		OK	OK						OK				RPR
70	19										SD		OK	OK						OK				RPR
71	20										PS		OK	OK						OK				RPR
72	21										UNS		OK	OK						OK				RPR
73	22										SC		OK	OK						OK				RPR
74	23										OK		OK	OK						MRF				RPR
75	24										OK		OK	OK						PIT				RPR
76	25										OK		OK	OK						WT				RPR
77	26										OK		OK	OK						CT				RPR
78	27										OK		OK	OK						GT				RPR
79	28										OK		OK	OK						WO				RPR
80	29										OK		OK	OK						BT				RPR
81	30										OK		OK	OK						BV				RPR
82	31										OK		OK	OK						BM				RPR
83	32										OK		OK	OK						DIM				RPR
84	33										OK		OK	OK						PUT				RPR
85	34										OK		OK	OK						CS				RPR
86	35										OK		OK	OK						GS				RPR
87	36										OK		OK	OK						SD				RPR
88	37										OK		OK	OK						PS				RPR
89	38										OK		OK	OK						UNS				RPR
90	39										OK		OK	OK						SC				RPR
91	40										OK		F	OK						OK				OK-HB
92	41										OK		OK	F						OK				OK-HB
93	42										OK		D	OK						OK				OK-HB
94	43										OK		OK	D						OK				OK-HB
95	44										OK		D	F						OK				OK-HB
96	45										OK		F	D						OK				OK-HB
97	46										OK		OK	OK						OK				OK
98	47										OK-FR		OK	OK						OK				OK
99	48										OK		OK	OK						OK-FR				OK
Drill Pipe

Cell Formulas
Range	Formula
X57:X90,X97:X99	X57	=IF(OR(K57="",T57=""),"",IF(AND(OR(K57="OK",K57="OK-FR"),OR(T57="OK",T57="OK-FR")),"OK","RPR"))
W90:W99	W90	=IF(E90="","",IF(E90<=80%,"2","P"))
E52:E99	E52	= IF([@[Min Wall]]="","",[@[Min Wall]]/$R$36)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K52:K1551,T52:T1551	Cell Value	contains "CT"	text	NO
P52:P1551	Cell Value	>General!$O$16	text	NO
X52:X1551	Cell Value	beginning with "REJ"	text	NO
X52:X1551	Cell Value	ending with "HB"	text	NO
X52:X1551	Cell Value	beginning with "RPR"	text	NO
B52:B1551	Cell Value	duplicates	text	NO
E52:E1551	Cell Value	between 0.7 and 0.7999	text	NO
E52:E1551	Cell Value	between 0.6 and 0.6999	text	NO
E52:E1551	Cell Value	between 0.001 and 0.5999	text	NO
V52:V1551	Expression	=IF($V52="X",TRUE,FALSE)	text	NO
K52:K1551	Expression	=IF($K52="MRF",TRUE,FALSE)	text	NO
T52:T1551	Expression	=IF($T52="MRF",TRUE,FALSE)	text	NO
N52:N1551	Expression	=IF($N52="D",TRUE,FALSE)	text	NO
M52:M1551	Expression	=IF($M52="D",TRUE,FALSE)	text	NO
N52:N1551	Expression	=IF(OR($N52="F",$N52="W"),TRUE, FALSE)	text	NO
M52:M1551	Expression	=IF(OR($M52="F",$M52="W"),TRUE,FALSE)	text	NO
R52:R1551	Cell Value	between 0.0001 and $W$42 - 0.0001	text	NO
R52:R1551	Cell Value	>$W$43	text	NO
I52:I1551	Cell Value	between 0.0001 and $W$40 - 0.00001	text	NO
I52:I1551	Cell Value	>$W$41	text	NO
S52:S1551	Cell Value	between $R$44 and $R$44+0.75	text	NO
S52:S1551	Cell Value	between 0.001 and ($R$44-0.001)	text	NO
J52:J1551	Cell Value	between $R$43 and $R$43+0.75	text	NO
J52:J1551	Cell Value	between 0.001 and $R$43-0.001	text	NO
G52:G1551,O52:O1551	Cell Value	between $R$41 and $R$41+0.0625	text	NO
G52:G1551,O52:O1551	Cell Value	between 0.001 and $R$41-0.001	text	NO
E52:E1551,M52:N1551,K52:K1551,T52:T1551	Expression	=IF(OR(E52=$E$24:$E$25),TRUE,FALSE)	text	NO
E52:E1551,M52:N1551,K52:K1551,T52:T1551	Expression	=IF(OR(E52=$E$8:$E$23),TRUE,FALSE)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
V52:V99	List	=Lists!$K$21
W68:W99	List	=Lists!$K$14:$K$18
F52:F99	List	=Lists!$K$24:$K$28
K68:K99	List	=Lists!$J$3:$J$23
T74:T99	List	=Lists!$J$3:$J$23
X52:X99	List	=Lists!$K$2:$K$5
M91:N99	List	=Lists!$K$8:$K$10