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]

OKay, I"ve digested your first comment to get the calculations for Column X. But there isn't any dialog about how you wnat to tabulate the columns into the first row? How do you want to tabulate the various values in a column in one cell (the cell at the top of each column? I also have questions about the calculations. Please see below:

Mr Excel Questions.xlsx
	A	B	C	D
1	column X is OK, OK-HB, RPR or REJ is based on what is inputted in column K, M, N, T & V
2	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
3	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
4
5	Column	Valid Values	Dependent on Columns:	Rules
6	X	OK,OK-HB,RPR,REJ	K,M,N,T,V
7				IF (Column K or T) = {STS,UND}, then Column X = REJ
8				IF (Column K or T) = {OK,OK-FR} AND (Column M or N) = {F or D} then Column X = OK-HB
9
10	So according to the rules in D7:D8 the dependend columns are K,T,M,N… where is Column V in calculating Column X?
11	Also according to the rules, the only values that will be populated in Column X are REJ and OK-HB … How are OK and RPR Calcuated?
Sheet4

 

[OKCIrish]

I'm not sure if I am understanding your first set of questions about how I want to tabulate the columns into the first row? Most of the information would be selected from a conditional formatting and/or manual input. Sorry if that's not the answer you are looking for.

The rules for column X to return as RPR would be:
IF (Column K or T) = {MRF,PIT,WT,CT,GT,WO,BV,BM,DIM,PUT,CS,GS,SD,PS,UNS,SC} AND/OR (Column V) = {X}, then column X = RPR
The rules for colmn X to return as OK would be
IF (Column K and T) = {OK, OK-FR} AND (Column M (Sometimes N) = {OK} , then column X = OK
I say sometimes for column N because it's about 50/50 if column N actually has to be tabulated.

 

[awoohaw]

I'm not sure if I am understanding your first set of questions about how I want to tabulate the columns into the first row? Most of the information would be selected from a conditional formatting and/or manual input. Sorry if that's not the answer you are looking for.

The rules for column X to return as RPR would be:
IF (Column K or T) = {MRF,PIT,WT,CT,GT,WO,BV,BM,DIM,PUT,CS,GS,SD,PS,UNS,SC} AND/OR (Column V) = {X}, then column X = RPR
The rules for colmn X to return as OK would be
IF (Column K and T) = {OK, OK-FR} AND (Column M (Sometimes N) = {OK} , then column X = OK
I say sometimes for column N because it's about 50/50 if column N actually has to be tabulated.

This is one of your previoius comments:

"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."

What do you mean by the underlined statement? Ultimately, what do you WANT help with? You don't seem to want an XLOOKUP function. But you have not explained anything else? What do you want to accomplish and what are the problems you are running into in getting there?

 

[awoohaw]

What do you mean by column being (50/50)?

IF (Column K and T) = {OK, OK-FR} AND (Column M (Sometimes N) = {OK} , then column X = OK _____ by Sometime N. = OK.. In this K and T situation if there is ever an N = OK do you still want X = OK

I say sometimes for column N because it's about 50/50 if column N actually has to be tabulated. By 50/50... what do you mean by "tabulated"

 

[OKCIrish]

This is one of your previoius comments:


What do you mean by the underlined statement? Ultimately, what do you WANT help with? You don't seem to want an XLOOKUP function. But you have not explained anything else? What do you want to accomplish and what are the problems you are running into in getting there?

Sorry for the confusion, I wasn't sharing all the rows on my work sheet, rows 1 through 48, because all that is on those rows are used as =COUNT functions for what is being inputted into the sheet. Like how many STS, UND, GT, OK, OK-HB, RPR, REJ etcetera are being inputted, that's all I was stating with that sentence.

What do you mean by column being (50/50)?

IF (Column K and T) = {OK, OK-FR} AND (Column M (Sometimes N) = {OK} , then column X = OK _____ by Sometime N. = OK.. In this K and T situation if there is ever an N = OK do you still want X = OK

I say sometimes for column N because it's about 50/50 if column N actually has to be tabulated. By 50/50... what do you mean by "tabulated"

What I mean by the column being 50/50 is that not all the time will it be required for column N to have OK, F, or D in it.

What I ultimately want help with is how I can get column X to recognize key words or inputs in columns K, M, N, T, V and return one of the four texts (OK, OK-HB, RPR & REJ). I was told that the best way to go about this without a bunch of nested formulas is creating an XLOOKUP table with those parameters laid out. I am not sure on how to approach or create that table.

 

[awoohaw]

I don' think lookups will work for you since the desired values in column X vary and you have values affecting it from multiple columns. I'll get some calculated columns done for you. Bear with me a few hours (i'm watching the football game).

 

[awoohaw]

Ok... here are the mapping rules for Column X.
I have a question about Column V being an "X"... If the values for K and T are always the same whether V has an X or not, why do you check Column V and map it to the same value for the X Column?

And the more I look at this, lookups of some sort will be easier than my first thoughts.

Please look and let me know if this contains all the mapping for Column X:

Book3
	A	B	C	D	E	F
1	K-Box Initial Condition	T-Pin Initial Cond	V-Bent	M-Box Hard Band	N-Pin HB	Map X to
2	BM		X			RPR
3	BM					RPR
4	BV		X			RPR
5	BV					RPR
6	CS		X			RPR
7	CS					RPR
8	CT		X			RPR
9	CT					RPR
10	DIM		X			RPR
11	DIM					RPR
12	GS		X			RPR
13	GS					RPR
14	GT		X			RPR
15	GT					RPR
16	MRF		X			RPR
17	MRF					RPR
18	OK			F		OK-HB
19	OK				F	OK-HB
20	OK-FR			D		OK-HB
21	OK-FR				D	OK-HB
22	PIT		X			RPR
23	PIT					RPR
24	PS		X			RPR
25	PS					RPR
26	PUT		X			RPR
27	PUT					RPR
28	SC		X			RPR
29	SC					RPR
30	SD		X			RPR
31	SD					RPR
32	STS					REJ
33	UND					REJ
34	UNS		X			RPR
35	UNS					RPR
36	WO		X			RPR
37	WO					RPR
38	WT		X			RPR
39	WT					RPR
40		BM	X			RPR
41		BV	X			RPR
42		CS	X			RPR
43		CT	X			RPR
44		DIM	X			RPR
45		GS	X			RPR
46		GT	X			RPR
47		MRF	X			RPR
48		PIT	X			RPR
49		PS	X			RPR
50		PUT	X			RPR
51		SC	X			RPR
52		SD	X			RPR
53		UNS	X			RPR
54		WO	X			RPR
55		WT	X			RPR
56		BM				RPR
57		BV				RPR
58		CS				RPR
59		CT				RPR
60		DIM				RPR
61		GS				RPR
62		GT				RPR
63		MRF				RPR
64		OK				OK-HB
65		OK				OK-HB
66		OK-FR				OK-HB
67		OK-FR				OK-HB
68		PIT				RPR
69		PS				RPR
70		PUT				RPR
71		SC				RPR
72		SD				RPR
73		STS				REJ
74		UND				REJ
75		UNS				RPR
76		WO				RPR
77		WT				RPR
XLookup Start

 

[awoohaw]

Also, many of the columns have data that are not considered for the value to calculate for column X, particularly columns K and T. That is fine and don't have to trigger a change in Column X, I just need to accomodate it.

 

[OKCIrish]

I noticed a typo of some info that may have not been put in correctly, pertaining to column 64-67 and fixed it below. The thing with column V is that if column all of the other columns are marked OK or OK-FR (K, M, N and T) and this one gets marked X then it automatically makes it a RPR.

Book1
	A	B	C	D	E	F
1	K-Box Initial Condition	T-Pin Initial Cond	V-Bent	M-Box Hard Band	N-Pin HB	Map X to
2	BM		X			RPR
3	BM					RPR
4	BV		X			RPR
5	BV					RPR
6	CS		X			RPR
7	CS					RPR
8	CT		X			RPR
9	CT					RPR
10	DIM		X			RPR
11	DIM					RPR
12	GS		X			RPR
13	GS					RPR
14	GT		X			RPR
15	GT					RPR
16	MRF		X			RPR
17	MRF					RPR
18	OK			F		OK-HB
19	OK				F	OK-HB
20	OK-FR			D		OK-HB
21	OK-FR				D	OK-HB
22	PIT		X			RPR
23	PIT					RPR
24	PS		X			RPR
25	PS					RPR
26	PUT		X			RPR
27	PUT					RPR
28	SC		X			RPR
29	SC					RPR
30	SD		X			RPR
31	SD					RPR
32	STS					REJ
33	UND					REJ
34	UNS		X			RPR
35	UNS					RPR
36	WO		X			RPR
37	WO					RPR
38	WT		X			RPR
39	WT					RPR
40		BM	X			RPR
41		BV	X			RPR
42		CS	X			RPR
43		CT	X			RPR
44		DIM	X			RPR
45		GS	X			RPR
46		GT	X			RPR
47		MRF	X			RPR
48		PIT	X			RPR
49		PS	X			RPR
50		PUT	X			RPR
51		SC	X			RPR
52		SD	X			RPR
53		UNS	X			RPR
54		WO	X			RPR
55		WT	X			RPR
56		BM				RPR
57		BV				RPR
58		CS				RPR
59		CT				RPR
60		DIM				RPR
61		GS				RPR
62		GT				RPR
63		MRF				RPR
64		OK		F		OK
65		OK			D	OK
66		OK-FR		F		OK
67		OK-FR			D	OK
68		PIT				RPR
69		PS				RPR
70		PUT				RPR
71		SC				RPR
72		SD				RPR
73		STS				REJ
74		UND				REJ
75		UNS				RPR
76		WO				RPR
77		WT				RPR
Sheet1

 

[awoohaw]

I noticed a typo of some info that may have not been put in correctly, pertaining to column 64-67 and fixed it below. The thing with column V is that if column all of the other columns are marked OK or OK-FR (K, M, N and T) and this one gets marked X then it automatically makes it a RPR.

Book1
	A	B	C	D	E	F
1	K-Box Initial Condition	T-Pin Initial Cond	V-Bent	M-Box Hard Band	N-Pin HB	Map X to
2	BM		X			RPR
3	BM					RPR
4	BV		X			RPR
5	BV					RPR
6	CS		X			RPR
7	CS					RPR
8	CT		X			RPR
9	CT					RPR
10	DIM		X			RPR
11	DIM					RPR
12	GS		X			RPR
13	GS					RPR
14	GT		X			RPR
15	GT					RPR
16	MRF		X			RPR
17	MRF					RPR
18	OK			F		OK-HB
19	OK				F	OK-HB
20	OK-FR			D		OK-HB
21	OK-FR				D	OK-HB
22	PIT		X			RPR
23	PIT					RPR
24	PS		X			RPR
25	PS					RPR
26	PUT		X			RPR
27	PUT					RPR
28	SC		X			RPR
29	SC					RPR
30	SD		X			RPR
31	SD					RPR
32	STS					REJ
33	UND					REJ
34	UNS		X			RPR
35	UNS					RPR
36	WO		X			RPR
37	WO					RPR
38	WT		X			RPR
39	WT					RPR
40		BM	X			RPR
41		BV	X			RPR
42		CS	X			RPR
43		CT	X			RPR
44		DIM	X			RPR
45		GS	X			RPR
46		GT	X			RPR
47		MRF	X			RPR
48		PIT	X			RPR
49		PS	X			RPR
50		PUT	X			RPR
51		SC	X			RPR
52		SD	X			RPR
53		UNS	X			RPR
54		WO	X			RPR
55		WT	X			RPR
56		BM				RPR
57		BV				RPR
58		CS				RPR
59		CT				RPR
60		DIM				RPR
61		GS				RPR
62		GT				RPR
63		MRF				RPR
64		OK		F		OK
65		OK			D	OK
66		OK-FR		F		OK
67		OK-FR			D	OK
68		PIT				RPR
69		PS				RPR
70		PUT				RPR
71		SC				RPR
72		SD				RPR
73		STS				REJ
74		UND				REJ
75		UNS				RPR
76		WO				RPR
77		WT				RPR
Sheet1

so, is what you have above the criteria you want? Just to confirm, please.