# List values based on rows

1. ## List values based on rows

This is one of those were a vlookup won't cut it. My data looks like this

G -- H -- I
16337 --XX--XX
16337-- 238447-- PI009313
16337-- 238836--XX

16337--XX--XX
16337-- 238447-- PI009313
16337-- 238836--XX

41524--XX--XX
41524--XX-- #VALUE!

41524--XX--XX
41524--XX-- #VALUE!

The -- separates columns in the example, and the XX signifies null cells. I need to build a formula to list of any Column G values, preferably unique, where Column I is #VALUE!. I need a formula because the list will be dynamic and may contain 10K + values altogether, but maybe 100 of those will have the error code.

2. ## Re: List values based on rows

Well, I am not really sure if formula is the best concept. Ahe you tried advanced filter with duplicates removal?

If you want formula try the array one (comitted Ctrl+Shift+Enter - not just enter) in L2:

``Please Login or Register  to view this content.``
and copy down

If you allow duplicates it will be substantially shorter (again - array formula):

``Please Login or Register  to view this content.``

3. ## Re: List values based on rows

Thanks. Tried that, didn't work. I'm halfway there with this array formula which gives me the row numbers for matching cells:

=LARGE(IF(ISERROR(\$I\$1:\$I\$199999),ROW(\$I\$1:\$I\$199999)),ROW(1:1))

Now I'm trying to remember the formula to use to pull the value from column G

4. ## Re: List values based on rows

GOT IT!!! Thanks for your help!

Array formula:
=INDEX(\$G\$1:\$I\$199999,LARGE(IF(ISERROR(\$I\$1:\$I\$199999),ROW(!\$I\$1:\$I\$199999)),ROW(1:1)),1)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1