Help using OR when Indexing

**AceForSale** · 03-09-2018, 02:53 PM

I need assistance using the Or command or another type of OR statement to allow me to index anything that is NOT in cells A2 and A3

Current code: (Using Cntl Shift Enter)

=IFERROR(INDEX('Sheet1'!A:A,SMALL(IF(
('Sheet1'!$G$2:$G$2001<>'ReportSheet'!$A$2),
ROW('Sheet1'!$G$2:$G$2001),""),ROWS(D$2:D2))),"")

On Line 2: its saying search Sheet1 in Column G for anything else other than whats in cell 'ReportSheet'!$A$2. (Ex: Apple)

The issue is, I need it to search for anything other than 'ReportSheet'!$A$2 & 'ReportSheet'!$A$3 (Essentially anything other than these 2 items then display it, Ex: Any results other than Apple or Banana)

I tried adding the Or option but then nothing shows up.

I did the Cntl Shift Enter
=IFERROR(INDEX('Sheet1'!A:A,SMALL(IF(
('Sheet1'!$G$2:$G$2001<>OR('ReportSheet'!$A$2,'ReportSheet'!$A$3)),
ROW('Sheet1'!$G$2:$G$2001),""),ROWS(D$2:D2))),"")

Please advise,
Thank you.

**63falcondude** · 03-09-2018, 03:05 PM

Maybe this?

{=IFERROR(INDEX('Sheet1'!A:A,SMALL(IF(
('Sheet1'!$G$2:$G$2001<>'ReportSheet'!$A$2)*('Sheet1'!$G$2:$G$2001<>'ReportSheet'!$A$3)
,ROW('Sheet1'!$G$2:$G$2001),""),ROWS(D$2:D2))),"")}

**tim201110** · 03-09-2018, 03:05 PM

=IFERROR(INDEX('Sheet1'!A:A,aggregate(15,6,ROW('Sheet1'!$G$2:$G$2001)/isna(match('Sheet1'!$G$2:$G$2001,'ReportSheet'!$A$2:$A$3,)),ROWS(D$2:D2))),"")

**shg** · 03-09-2018, 03:07 PM

=IFERROR(INDEX(Sheet1!A:A, SMALL(IF((Sheet1!$G$2:$G$2001 <> ReportSheet!$A$2) * (Sheet1!$G$2:$G$2001 <> ReportSheet!$A$3),
ROW(Sheet1!$G$2:$G$2001),""), ROWS(D$2:D2))), "")

**AceForSale** · 03-09-2018, 03:59 PM

Thank you 63falcondude and shg, it worked.

tim201110 for some reason yours didn't work. I've never used the aggregate before. What was the 15,6 for?
Thanks

**63falcondude** · 03-09-2018, 04:05 PM

You're welcome. Glad we could help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.