Extract Unique Values from Lists INDEX function

Hi everyone,

Previously I have built an excel sheet that takes a user input and searches through three columns independently.

I had used the same INDEX(SMALL(...SEARCH( ... ))) based function to find this input in each of the columns. I then tried to use an array function to derive the unique values. I tried both...

=IFERROR(INDEX((...),MATCH(0,COUNTIF(...)),0)), "") as well as
=IFERROR(INDEX((...),MATCH(0,IF(ISBLANK((...)),1,COUNTIF(...))),0)), "") to account for blank spaces. Neither seem to work and I think this is because I used multiple ranges.

Any suggestions would be appreciated. Thank you!

Ray

Okay, so we have this table from B3:F15 and we want to extract "unique values"? Unique in what sense? You said you expect 2,3,4,6 and 12 but, except the date, 2 and 4 look identical. Can you explain the criteria a bit more?

Sorry to be unclear! The new example below should be much easier to understand.

I wanted to use a function to extract unique values from the ranges $J$5:$J$13,$J$15:$J$23,$M$5:$M$13. In my example Table 2 outputs 3, 6, 12; Table 3 outputs 12. Since 12 appeared twice, this value is unique only once. Thus the unique values are 3, 6, 12. (12 does not appear twice)

Essentially the list is 2,3,4,6,12,3,6,12,12 and I would like to output only 2, 3, 4, 6, 12. I think one solution is that I create a single range by setting the cells equal. (Z5 = J5, Z6 = J6...) and apply my original function to a single range. (Just thought of this)

Does this clarify?

The only portion of the example that I am unable to get to work properly is B25:B38. Thank you!

I created a unique list in G using (in G4 copied down)
=IF(ISNUMBER(MATCH("*"&$J$3&"*", $D4:$F4,0)),COUNT($G$3:$G3)+1,"")

Then in B25 copied down
=IFERROR(INDEX($B$4:$B$15, MATCH(ROWS($A$1:$A1), $G$4:$G$15,0)),"")
Is that acceptable?

This is very different. In the first example, each row represented a unique record, whether the criteria appeared in D,E, or F. In this example, each cell represents a unique record so you can pull multiple records from the same row.

You said that the database is stored across several workbooks. Can you give an example of that, using separate worksheets instead of workbooks. Also, if you will be working with strings, best to have your example in strings.