Question: re ... =IF(A2="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C2,K$2:K$30))),"",G2)

[jamada]

I don't know why, but it doesn't find "BOS" in Range K regardless of where I put it?


=IF(A2="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C2,K$2:K$30))),"",G2)

Excel 2010
	K
1
2	YTZ
3	DFW
4	PHL
5	YYZ
6	YXE
7	YWG
8	YUL
9	YQR
10	YQM
11	YOW
12	YHZ
13	ORD
14	LGA
15	EWR
16	DCA
17	BOS
OCTOBER

Cell Formulas
Range	Formula
K2	YTZ
K3	DFW
K4	PHL
K5	YYZ
K6	YXE
K7	YWG
K8	YUL
K9	YQR
K10	YQM
K11	YOW
K12	YHZ
K13	ORD
K14	LGA
K15	EWR
K16	DCA
K17	BOS

 

[par60056]

Look at your Match() call should that be A2 not C2? I suspect a little copy/paste issue in building the formula. Your sample data only shows column K not A or C

 

[mole999]

unintended spaces somewhere ?

 

[jamada]

Col C is my data that may change, My range check list is within Column K

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1								Accountable Stations Delays	YTZ/DFW/PHL CONTROLABLE DELAY
2	7351	10/18/14	DCA	YYZ	E75	FOP	FOC	FOC		FOC	YTZ
3	7351	10/11/14	DCA	YYZ	E75	PSR	PSU	PSU		PSU	DFW
4	7351	10/02/14	DCA	YYZ	E75	ACS	ACL	ACL		ACL	PHL
5	7351	10/08/14	DCA	YYZ	E75	FOP	FOA	FOA		FOA	YYZ
6	7352	10/03/14	YYZ	DCA	E75	ISS	ISS	ISS		ISS	YXE
7	7352	10/16/14	YYZ	DCA	E75	SEC	SEB	SEB		SEB	YWG
8	7352	10/19/14	YYZ	DCA	E75	IFS	IFU	IFU		IFU	YUL
9	7353	10/20/14	DCA	YYZ	E75	ACS	ACG	ACG		ACG	YQR
10	7353	10/12/14	DCA	YYZ	E75	ACS	ACG	ACG		ACG	YQM
11	7353	10/11/14	DCA	YYZ	E75	MTC	MTF	MTF		MTF	YOW
12	7353	10/01/14	BOS	YYZ	E75	ISS	ISS				YHZ
13	7354	10/20/14	YYZ	DCA	E75	SEC	SEI	SEI		SEI	ORD
14	7354	10/26/14	YYZ	DCA	E75	MTC	MTF	MTF		MTF	LGA
15	7354	10/10/14	YYZ	DCA	E75	LOD	LOD	LOD		LOD	EWR
16	7354	10/24/14	YYZ	DCA	E75	LOD	LOD	LOD		LOD	DCA
17	7355	10/26/14	DCA	YYZ	E75	PSR	PCH	PCH		PCH	BOS
OCTOBER

Cell Formulas
Range	Formula
H2	=IF(A2="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C2,K$2:K$30))),"",G2))
H3	=IF(A3="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C3,K$2:K$30))),"",G3))
H4	=IF(A4="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C4,K$2:K$30))),"",G4))
H5	=IF(A5="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C5,K$2:K$30))),"",G5))
H6	=IF(A6="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C6,K$2:K$30))),"",G6))
H7	=IF(A7="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C7,K$2:K$30))),"",G7))
H8	=IF(A8="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C8,K$2:K$30))),"",G8))
H9	=IF(A9="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C9,K$2:K$30))),"",G9))
H10	=IF(A10="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C10,K$2:K$30))),"",G10))
H11	=IF(A11="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C11,K$2:K$30))),"",G11))
H12	=IF(A12="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C12,K$2:K$30))),"",G12))
H13	=IF(A13="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C13,K$2:K$30))),"",G13))
H14	=IF(A14="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C14,K$2:K$30))),"",G14))
H15	=IF(A15="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C15,K$2:K$30))),"",G15))
H16	=IF(A16="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C16,K$2:K$30))),"",G16))
H17	=IF(A17="","",IF(ISNA(INDEX(K$2:K$30,MATCH(C17,K$2:K$30))),"",G17))
I2	=IF($A2="","",IF(AND(OR($F2="ACS",$F2="CGO",$F2="GND",$F2="PSR"),OR($C2="YTZ",$C2="PHL",$C2="DFW")), $G2,""))
I3	=IF($A3="","",IF(AND(OR($F3="ACS",$F3="CGO",$F3="GND",$F3="PSR"),OR($C3="YTZ",$C3="PHL",$C3="DFW")), $G3,""))
I4	=IF($A4="","",IF(AND(OR($F4="ACS",$F4="CGO",$F4="GND",$F4="PSR"),OR($C4="YTZ",$C4="PHL",$C4="DFW")), $G4,""))
I5	=IF($A5="","",IF(AND(OR($F5="ACS",$F5="CGO",$F5="GND",$F5="PSR"),OR($C5="YTZ",$C5="PHL",$C5="DFW")), $G5,""))
I6	=IF($A6="","",IF(AND(OR($F6="ACS",$F6="CGO",$F6="GND",$F6="PSR"),OR($C6="YTZ",$C6="PHL",$C6="DFW")), $G6,""))
I7	=IF($A7="","",IF(AND(OR($F7="ACS",$F7="CGO",$F7="GND",$F7="PSR"),OR($C7="YTZ",$C7="PHL",$C7="DFW")), $G7,""))
I8	=IF($A8="","",IF(AND(OR($F8="ACS",$F8="CGO",$F8="GND",$F8="PSR"),OR($C8="YTZ",$C8="PHL",$C8="DFW")), $G8,""))
I9	=IF($A9="","",IF(AND(OR($F9="ACS",$F9="CGO",$F9="GND",$F9="PSR"),OR($C9="YTZ",$C9="PHL",$C9="DFW")), $G9,""))
I10	=IF($A10="","",IF(AND(OR($F10="ACS",$F10="CGO",$F10="GND",$F10="PSR"),OR($C10="YTZ",$C10="PHL",$C10="DFW")), $G10,""))
I11	=IF($A11="","",IF(AND(OR($F11="ACS",$F11="CGO",$F11="GND",$F11="PSR"),OR($C11="YTZ",$C11="PHL",$C11="DFW")), $G11,""))
I12	=IF($A12="","",IF(AND(OR($F12="ACS",$F12="CGO",$F12="GND",$F12="PSR"),OR($C12="YTZ",$C12="PHL",$C12="DFW")), $G12,""))
I13	=IF($A13="","",IF(AND(OR($F13="ACS",$F13="CGO",$F13="GND",$F13="PSR"),OR($C13="YTZ",$C13="PHL",$C13="DFW")), $G13,""))
I14	=IF($A14="","",IF(AND(OR($F14="ACS",$F14="CGO",$F14="GND",$F14="PSR"),OR($C14="YTZ",$C14="PHL",$C14="DFW")), $G14,""))
I15	=IF($A15="","",IF(AND(OR($F15="ACS",$F15="CGO",$F15="GND",$F15="PSR"),OR($C15="YTZ",$C15="PHL",$C15="DFW")), $G15,""))
I16	=IF($A16="","",IF(AND(OR($F16="ACS",$F16="CGO",$F16="GND",$F16="PSR"),OR($C16="YTZ",$C16="PHL",$C16="DFW")), $G16,""))
I17	=IF($A17="","",IF(AND(OR($F17="ACS",$F17="CGO",$F17="GND",$F17="PSR"),OR($C17="YTZ",$C17="PHL",$C17="DFW")), $G17,""))
J2	=IF($A2="","",IF(AND(OR($F2="ACS",$F2="CGO",$F2="GND",$F2="PSR"),OR($C2="YTZ",$C2="PHL",$C2="DFW")),I2,H2))
J3	=IF($A3="","",IF(AND(OR($F3="ACS",$F3="CGO",$F3="GND",$F3="PSR"),OR($C3="YTZ",$C3="PHL",$C3="DFW")),I3,H3))
J4	=IF($A4="","",IF(AND(OR($F4="ACS",$F4="CGO",$F4="GND",$F4="PSR"),OR($C4="YTZ",$C4="PHL",$C4="DFW")),I4,H4))
J5	=IF($A5="","",IF(AND(OR($F5="ACS",$F5="CGO",$F5="GND",$F5="PSR"),OR($C5="YTZ",$C5="PHL",$C5="DFW")),I5,H5))
J6	=IF($A6="","",IF(AND(OR($F6="ACS",$F6="CGO",$F6="GND",$F6="PSR"),OR($C6="YTZ",$C6="PHL",$C6="DFW")),I6,H6))
J7	=IF($A7="","",IF(AND(OR($F7="ACS",$F7="CGO",$F7="GND",$F7="PSR"),OR($C7="YTZ",$C7="PHL",$C7="DFW")),I7,H7))
J8	=IF($A8="","",IF(AND(OR($F8="ACS",$F8="CGO",$F8="GND",$F8="PSR"),OR($C8="YTZ",$C8="PHL",$C8="DFW")),I8,H8))
J9	=IF($A9="","",IF(AND(OR($F9="ACS",$F9="CGO",$F9="GND",$F9="PSR"),OR($C9="YTZ",$C9="PHL",$C9="DFW")),I9,H9))
J10	=IF($A10="","",IF(AND(OR($F10="ACS",$F10="CGO",$F10="GND",$F10="PSR"),OR($C10="YTZ",$C10="PHL",$C10="DFW")),I10,H10))
J11	=IF($A11="","",IF(AND(OR($F11="ACS",$F11="CGO",$F11="GND",$F11="PSR"),OR($C11="YTZ",$C11="PHL",$C11="DFW")),I11,H11))
J12	=IF($A12="","",IF(AND(OR($F12="ACS",$F12="CGO",$F12="GND",$F12="PSR"),OR($C12="YTZ",$C12="PHL",$C12="DFW")),I12,H12))
J13	=IF($A13="","",IF(AND(OR($F13="ACS",$F13="CGO",$F13="GND",$F13="PSR"),OR($C13="YTZ",$C13="PHL",$C13="DFW")),I13,H13))
J14	=IF($A14="","",IF(AND(OR($F14="ACS",$F14="CGO",$F14="GND",$F14="PSR"),OR($C14="YTZ",$C14="PHL",$C14="DFW")),I14,H14))
J15	=IF($A15="","",IF(AND(OR($F15="ACS",$F15="CGO",$F15="GND",$F15="PSR"),OR($C15="YTZ",$C15="PHL",$C15="DFW")),I15,H15))
J16	=IF($A16="","",IF(AND(OR($F16="ACS",$F16="CGO",$F16="GND",$F16="PSR"),OR($C16="YTZ",$C16="PHL",$C16="DFW")),I16,H16))
J17	=IF($A17="","",IF(AND(OR($F17="ACS",$F17="CGO",$F17="GND",$F17="PSR"),OR($C17="YTZ",$C17="PHL",$C17="DFW")),I17,H17))

 

[jamada]

Ive checked for spaces and NO, not that either. have retyped BOS in both fields to no avail

 

[par60056]

Working through your formula. Try putting a ",0" for the 3rd parameter in the match(). I think there should be an easier way to do this.

 

[par60056]

You are not using the value from the index. Is Hx simply Gx if Cx is found in K?

 

[Aladin Akyurek]

What do you have in C2, the value MATCH is comparing with K2:K30? Also, what is in G2?

 

[jamada]

You are not using the value from the index. Is Hx simply Gx if Cx is found in K?

par60056...... YES

And it now works fine with your 3rd parameter suggestion ,"O".....

Ultimately I'm trying to get Hx to equal Gx IF Cx found in Range K2:K30, but if only Gx equals one of the contents (3 letter Code) found in Range L8:16.


But if Cx equals PHL or DFW or YTZ then Hx will equal Gx, ONLY if Fx equals CGO or GND or PSR or ACS.


Good grief I'm to green for this, but sure does keep the brain going!

Tks Graham