How to search an array for the smallest value then display another column, while removing duplicates

Adam Hetherwick

Hello,

I am trying to create a formula to search two columns of data, find the smallest value, and return the value that corresponds to that specific column, while removing duplicates.

Essentially, 'Lab List A' and 'Lab List B' are the two columns I want to sort by order, while removing duplicates. I created the extra columns in an attempt to shorten the equation. 'Lab List A' and 'Lab List B' are two different lists of labs that have duplicates and also some labs with letters at the end of the string. Everything I've tried so far only returns the values in 'Lab List A' and I think it's because I haven't solidified a concrete way of determining if the lab of interest is already included in the 'Lab Location' column. The 'LL' and 'Both Presence' columns are unnecessary for now. If you need any other information please let me know.

Thanks

Jeff Reisman

@Adam Hetherwick

Help me understand what you're trying to do:

"Search two columns of data, find the smallest value" - the lowest number between the two columns, or the lowest number for each column?

"Return the value that corresponds to that specific column" - so would this mean you want the lowest number for each column?

"While removing duplicates" - if you're looking for the lowest number from each column, why would you have duplicates in that result?

Where do you want these values displayed - in a particular cell, in a summary field, on another sheet?

"Essentially, 'Lab List A' and 'Lab List B' are the two columns I want to sort by order, while removing duplicates." - This seems like an entirely different ask, like you're looking for a list. Is that what you want, a list of the labs sorted in ascending numeric order without duplicates? If so, where should that list go - on this same sheet, on another sheet?

I can tell you that with the visible data you have, and the formula you show, with all the values in columns A and B being zero, you'll never return a value from list B:

If A@row = 1, then run this IF: IF B@row = 1, leave it blank, otherwise pull a value from Lab List B; If A@row <> 1, run this other index to pull values from Lab List A. - see, the logic won't get to the B range.

Not to mention your INDEX ranges each include data from 3 columns: [Lab List B]$2:[Lab List B Letters Removed]$20 includes the values in Lab List A Letters Removed, and vice versa. See the colored boxes in your screenshot? Green for B includes Lab List A Letters Removed, brown for A includes Lab List B Letters Removed.