Combine two index match formulas into single formula

[dejamls]

Hi all

I have these two formulas which work but I need to combine into one formula. I tried a few things but unsuccessful and given up. I could create helper columns but rather use single formula.

=IF($A11="","",IFERROR(IF(INDEX(PDA!$B$2:$B$1048576,MATCH(TRUE,ISNUMBER(SEARCH("*"&"Code Red"&"*",PDA!$Q$2:$Q$1048576)*SEARCH($A11,PDA!$B$2:$B$1048576)),0))=TRUE,"N","Y"),""))

=IF($A11="","",IFERROR(IF(INDEX(RDA!$B$2:$B$1048576,MATCH(TRUE,ISNUMBER(SEARCH("*"&"Code Red"&"*",RDA!$U$2:$U$1048576)*SEARCH($A11,RDA!$B$2:$B$1048576)),0))=TRUE,"N","Y"),""))

I need to combine them. Can someone help combine them please into one formula.

 

[awoohaw]

what do you want to do in the cell with the two values that each formula returns?

 

[dejamls]

Because $A11 value (i.e. document number) in a sheet called DOCUMENT could be present in in either sheet (PDA or RDI in their respective column B), if it finds it then will check another column in respective sheet (Q for PDA and U for RDI) for partial string match "code red". Then if found return "Y"or if not found "N".

Hope that makes sense.

 

[awoohaw]

so, you want ti to be an OR statement? Is the only difference in the formulas the Sheet Reference?

 

[dejamls]

Yes that is correct

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

How about

=IF($A11="","",IF(OR(IFERROR(INDEX(PDA!$B$2:$B$1048576,MATCH(TRUE,ISNUMBER(SEARCH(Code Red",PDA!$Q$2:$Q$1048576)*SEARCH($A11,PDA!$B$2:$B$1048576)),0)),""),IFERROR(INDEX(RDA!$B$2:$B$1048576,MATCH(TRUE,ISNUMBER(SEARCH("Code Red",RDA!$U$2:$U$1048576)*SEARCH($A11,RDA!$B$2:$B$1048576)),0)),"")),"N","Y"))

 

[dejamls]

How about

=IF($A11="","",IF(OR(IFERROR(INDEX(PDA!$B$2:$B$1048576,MATCH(TRUE,ISNUMBER(SEARCH(Code Red",PDA!$Q$2:$Q$1048576)*SEARCH($A11,PDA!$B$2:$B$1048576)),0)),""),IFERROR(INDEX(RDA!$B$2:$B$1048576,MATCH(TRUE,ISNUMBER(SEARCH("Code Red",RDA!$U$2:$U$1048576)*SEARCH($A11,RDA!$B$2:$B$1048576)),0)),"")),"N","Y"))

Hi Fluff. Tried it (added wild cards to "code red" text i.e. "*"&"Code Red"&"*" as it always appears in a string of text ), but I get a #VALUE error. Cheers

=IF($A11="","",IF(OR(IFERROR(INDEX(PDA!$B$2:$B$1048576,MATCH(TRUE,ISNUMBER(SEARCH("*"&"Code Red"&"*",PDA!$Q$2:$Q$1048576)*SEARCH($A11,PDA!$B$2:$B$1048576)),0)),""),IFERROR(INDEX(RDA!$B$2:$B$1048576,MATCH(TRUE,ISNUMBER(SEARCH("*"&"Code Red"&"*",RDA!$U$2:$U$1048576)*SEARCH($A11,RDA!$B$2:$B$1048576)),0)),"")),"N","Y"))

 

[Fluff]

added wild cards to "code red" text i.e. "*"&"Code Red"&"*"

You don't need them, search will look for cells that contain the value, not equals it.

Can you post some sample data from both sheets.

 

[dejamls]

DOC Sheet

SAMPLE WB.xlsx
	A	B	C	D	E	F
1
2
3
4
5
6
7
8
9
10	Document No	Document Title	Version No.	Dept Set	Area	Code Red Y / N
11	XYZ	TRUCK FORM				#VALUE!
12	FGH	WORK INSTRUCTION CHANGE REQUEST AND SIGN OFF FORM				#VALUE!
13	DFG	TRUCK PERFORMANCE REVIEW				#VALUE!
14	GHF	PROCESS CHANGE REQUEST FORM				#VALUE!
15	ABC	TRUCK INSPECTION				#VALUE!
16	YUH	TRUCK MAINT				#VALUE!
17	ZDG	PROCESS CHANGE REQUEST FORM				#VALUE!
DOC

Cell Formulas
Range	Formula
F11:F17	F11	=IF($A11="","",IF(OR(IFERROR(INDEX(PDA!$B$2:$B$1048576,MATCH(TRUE,ISNUMBER(SEARCH("Code Red",PDA!$Q$2:$Q$1048576)*SEARCH($A11,PDA!$B$2:$B$1048576)),0)),""),IFERROR(INDEX(RDA!$B$2:$B$1048576,MATCH(TRUE,ISNUMBER(SEARCH("Code Red",RDA!$U$2:$U$1048576)*SEARCH($A11,RDA!$B$2:$B$1048576)),0)),"")),"N","Y"))

PDA Sheet

SAMPLE WB.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Link	Document No	Title	Rev No	Status	Critical	Locked By	Lock Date	Last Rev By	Revision Date	Received Date	Review Period	Next Review	Lock Notes	Course Code	Notes	Keywords
2		XYZ	TRUCK FORM														INSPECTION, CODE RED, FORM
3		FGH	WORK INSTRUCTION CHANGE REQUEST AND SIGN OFF FORM														COVER SHEET, CHANGE REQUEST FORM, COD RED
4		DFG	TRUCK PERFORMANCE REVIEW														PA, MOC, FORM
5		GHF	PROCESS CHANGE REQUEST FORM														MOC
PDA

RDA Sheet

SAMPLE WB.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Link	Document No	Title	Rev No	Status	Revision Date	Received Date	Last Rev By	Review Period	Next Review	Locked By	Lock Date	Equipment No	Lock Notes	Owner	Dept	Vendor	Vendor Doc No	Project No	Notes	Keywords
2		ABC	TRUCK INSPECTION																		INSPECTION, CODE RED
3		YUH	TRUCK MAINT																		PA, MOC, FORM
4		ZDG	PROCESS CHANGE REQUEST FORM																		MOC
RDA