Index/Match Issues when using OR function

[Flanman6997]

STM_V_900_Template.xlsx
	A	B	C	D	E	F	G	H
5	GP-V/CP:	GP-V900	Target:	Orale	Alt. Target:	B-Actin
6
7	Sample Number:
8
9
10	Ct Data from File:			10TA Plate 1 Orale V900 SDS10 NPH02JUN2021
11	Threshold:			Orale:0.24247
12	Baseline:
13		Fill in all information where needed.
14
15
16	Qualifier Point			Controls
17	Copy Number			Control			RT
18	Target			Target			Orale
19	Average Ct			Average Ct			UND
20	SD			SD			UND
21	%CV	N/A		%CV	N/A	N/A	N/A	N/A
22	CV (%) - pass/fail	N/A		CV (%) - pass/fail	N/A	N/A	N/A	N/A
Data Sheet

Cell Formulas
Range	Formula
D10	D10	=IF('QuantStudio Raw'!B29="", "", 'QuantStudio Raw'!B29)
D11	D11	=IFERROR(INDEX('QuantStudio Raw'!E42:E138,MATCH("250"&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)),"")&IFERROR(IF(MATCH("250"&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0),":",""), "")&IFERROR(ROUND(INDEX('QuantStudio Raw'!P42:P138,MATCH("250"&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)), 5), "")&" "&IFERROR(INDEX('QuantStudio Raw'!E42:E138, MATCH("TA1"&F5, 'QuantStudio Raw'!D42:D138& 'QuantStudio Raw'!E42:E138, 0)), "")&IFERROR(IF(MATCH("TA1"&F5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0),":",""), "")&IFERROR(ROUND(INDEX('QuantStudio Raw'!P42:P138,MATCH("TA1"&F5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)), 5), "")
E17	E17	=IFERROR(IF(MATCH(OR("EC","EC1", "EC 1")&D5,'QuantStudio Raw'!D:D&'QuantStudio Raw'!E:E,0),"EC1",""), "")
F17	F17	=IFERROR(IF(MATCH(OR("EC2","EC 2")&D5,'QuantStudio Raw'!D:D&'QuantStudio Raw'!E:E,0),"EC2",""),"")
G17	G17	=IFERROR(IF(MATCH("RT"&D5,'QuantStudio Raw'!D:D&'QuantStudio Raw'!E:E,0),"RT",""), "")
H17	H17	=IFERROR(IF(MATCH(OR("EC","EC1", "EC 1")&F5,'QuantStudio Raw'!D:D&'QuantStudio Raw'!E:E,0),"EC1",""), "")
E18	E18	=IFERROR(INDEX('QuantStudio Raw'!E42:E138, MATCH(OR("EC1", "EC", "EC 1")&D5, 'QuantStudio Raw'!D42:D138& 'QuantStudio Raw'!E42:E138, 0)), "")
F18	F18	=IFERROR(INDEX('QuantStudio Raw'!E42:E138, MATCH(OR("EC2","EC 2")&D5, 'QuantStudio Raw'!D42:D138& 'QuantStudio Raw'!E42:E138, 0)), "")
G18	G18	=IFERROR(INDEX('QuantStudio Raw'!E42:E138, MATCH("RT"&D5, 'QuantStudio Raw'!D42:D138& 'QuantStudio Raw'!E42:E138, 0)), "")
H18	H18	=IFERROR(INDEX('QuantStudio Raw'!E42:E138, MATCH(OR("EC1", "EC", "EC 1")&F5, 'QuantStudio Raw'!D42:D138& 'QuantStudio Raw'!E42:E138, 0)), "")
E19	E19	=IFERROR(IF((INDEX('QuantStudio Raw'!J42:J138,MATCH(OR("EC1", "EC", "EC 1")&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!J42:J138,MATCH(OR("EC1", "EC", "EC 1")&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")
F19	F19	=IFERROR(IF((INDEX('QuantStudio Raw'!J42:J138,MATCH(OR("EC2","EC 2")&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!J42:J138,MATCH(OR("EC2","EC 2")&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")
G19	G19	=IFERROR(IF((INDEX('QuantStudio Raw'!J42:J138,MATCH("RT"&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!J42:J138,MATCH("RT"&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")
H19	H19	=IFERROR(IF((INDEX('QuantStudio Raw'!J42:J138,MATCH(OR("EC1", "EC", "EC 1")&F5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!J42:J138,MATCH(OR("EC1", "EC")&#REF!,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")
E20	E20	=IFERROR(IF((INDEX('QuantStudio Raw'!K42:K138,MATCH(OR("EC1", "EC", "EC 1")&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!K42:K138,MATCH(OR("EC1", "EC", "EC 1")&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")
F20	F20	=IFERROR(IF((INDEX('QuantStudio Raw'!K42:K138,MATCH(OR("EC2","EC 2")&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!K42:K138,MATCH(OR("EC2","EC 2")&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")
G20	G20	=IFERROR(IF((INDEX('QuantStudio Raw'!K42:K138,MATCH("RT"&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!K42:K138,MATCH("RT"&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")
H20	H20	=IFERROR(IF((INDEX('QuantStudio Raw'!K42:K138,MATCH(OR("EC1", "EC", "EC 1")&F5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!K42:K138,MATCH(OR("EC1", "EC")&#REF!,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")
E21:H21,B21	E21	=IF(ISERROR(E20/E19*100), "N/A", (E20/E19*100))
E22:H22,B22	E22	=IF(E21="N/A", "N/A", IF(E21<=10,"Pass","Fail"))
B17	B17	=IFERROR(IF(MATCH(OR("250","Plasmid", "250 copy", "250 cp", "250 plasmid", "plasmid 250")&#REF!,'QuantStudio Raw'!D:D&'QuantStudio Raw'!E:E,0),"250",""), "")
B18	B18	=IFERROR(INDEX('QuantStudio Raw'!E42:E138, MATCH(OR("250","Plasmid", "250 copy", "250 cp", "250 plasmid", "plasmid 250")&#REF!, 'QuantStudio Raw'!D42:D138& 'QuantStudio Raw'!E42:E138, 0)), "")
B19	B19	=IFERROR(IF((INDEX('QuantStudio Raw'!J42:J138,MATCH(OR("250","Plasmid", "250 copy", "250 cp", "250 plasmid", "plasmid 250")&#REF!,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!J42:J138,MATCH(OR("250","Plasmid", "250 copy", "250 cp", "250 plasmid", "plasmid 250")&#REF!,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")
B20	B20	=IFERROR(IF((INDEX('QuantStudio Raw'!K42:K138,MATCH(OR("250","Plasmid", "250 copy", "250 cp", "250 plasmid", "plasmid 250")&#REF!,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!K42:K138,MATCH(OR("250","Plasmid", "250 copy", "250 cp", "250 plasmid", "plasmid 250")&#REF!,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")

Hi all,

Here are the two Index/match equations I have been using for a data analysis templates I use at work:

=IFERROR(INDEX('QuantStudio Raw'!E42:E138, MATCH("TA1"&D5, 'QuantStudio Raw'!D42:D138& 'QuantStudio Raw'!E42:E138, 0)), "")

=IFERROR(IF((INDEX('QuantStudio Raw'!J42:J138,MATCH("TA1"&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!J42:J138,MATCH("TA1"&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")

I want to be able to include the option to search for "TA1" or "TA 1". So I changed these two equations above to the two below and I have been getting a #value! error:

=IFERROR(INDEX('QuantStudio Raw'!E42:E138, MATCH(OR("TA1","TA 1")&D5, 'QuantStudio Raw'!D42:D138& 'QuantStudio Raw'!E42:E138, 0)), "")

=IFERROR(IF((INDEX('QuantStudio Raw'!J42:J138,MATCH(OR("TA1","TA 1")&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0)))="","UND",INDEX('QuantStudio Raw'!J42:J138,MATCH(OR("TA1","TA 1")&D5,'QuantStudio Raw'!D42:D138&'QuantStudio Raw'!E42:E138,0))), "")

Please help

 

[sxhall]

Taking your initial formula...

=IFERROR(INDEX('QuantStudio Raw'!E42:E138, MATCH("TA1"&D5, 'QuantStudio Raw'!D42:D138& 'QuantStudio Raw'!E42:E138, 0)), "")

You have the OR function in the wrong place, try...

=IFERROR(OR(INDEX('QuantStudio Raw'!E42:E138, MATCH("TA1"&D5, 'QuantStudio Raw'!D42:D138& 'QuantStudio Raw'!E42:E138, 0)),INDEX('QuantStudio Raw'!E42:E138, MATCH("TA 1"&D5, 'QuantStudio Raw'!D42:D138& 'QuantStudio Raw'!E42:E138, 0)))"")

Same would apply for the other formula as well, they need be inside the OR function.

Steven