# Look up range, return to other columns

1. ## Look up range, return to other columns

I wonder if anyone could give me a formula to look up the range in C and return the data in B to N & O as attached please. :-) T.I.A.
I am missing something here?

2. ## Re: Look up range, return to other columns

Hi

In N1
=TRANSPOSE((UNIQUE(C2:C4,FALSE)))

In N2 copied to O2
=FILTER(\$B\$2:\$B\$4,\$C\$2:\$C\$4=G2)

However this is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy) so that we can consider a real world question. It strikes me that a Pivot Table may be a better option.

3. ## Re: Look up range, return to other columns

with N1="bb", do you want to find "hello" in O1 or N2 ?

4. ## Re: Look up range, return to other columns

Hi bsalv, thanks for reply so every then with gb would appear in column N and with bb would be in column O.

5. ## Re: Look up range, return to other columns

Posted in error

6. ## Re: Look up range, return to other columns

Richard, thankyou so much it works!! For future reference how would I add for example column A to the formula?

7. ## Re: Look up range, return to other columns

What would be in Column A? Whatever you put in Column A, change the reference in the current formula of where it is to Column A. Maybe I'm misunderstanding your question.

8. ## Re: Look up range, return to other columns

Originally Posted by And180y
Richard, thankyou so much it works!! For future reference how would I add for example column A to the formula?
How would you expect the output to look? At the moment you essentially have a 2 dimensional range which you want to transpose with unique items in a column resulting as unique items in a row, and underneath the result columns you list the column B items. IT's not clear how a 3rd column A is to be shown

I need to see the result. Please add and reupload.

9. ## Re: Look up range, return to other columns

Hi Richard,
sorry for delay. Using Filter wont work on my laptop.
I have tried as per the attached sheet to use Vlookup and seperately Index Match
but must be missing something.

10. ## Re: Look up range, return to other columns

In I2

=IFERROR(INDEX(\$B\$2:\$B\$15,AGGREGATE(15,6,ROW(\$A\$1:\$A\$100)/(\$A\$2:\$A\$15=\$I\$1),ROWS(\$1:1))),"")

in J2

=IFERROR(INDEX(\$B\$2:\$B\$15,AGGREGATE(15,6,ROW(\$A\$1:\$A\$100)/(\$A\$2:\$A\$15=\$j\$1),ROWS(\$1:1))),"")

copy both down

11. ## Re: Look up range, return to other columns

Originally Posted by And180y
Hi Richard,
sorry for delay. Using Filter wont work on my laptop.
If you have 365 as your profile shows then there's no reason why the new SPILL functions like FILTER won't work.

#### Thread Information

##### Users Browsing this Thread

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