# A formula to return a list

1. ## A formula to return a list

Hi Guys,

Wondering how I would go about the following.

Sheet 1 Column A1 Customers Column B1 Products
A2 Mickey B2 Apple
A3 Donald B3 Orange
A4 Daisey B4 Banana

Sheet 2 Column A1 Manually type in (Customers or Products)
A2 (Formula) (look up sheet2 A1 in Sheet 1 and return 1 below it)
A3 (Formula) (look up sheet2 A1 in Sheet 1 and return 2 below it)
A4 (Formula) (look up sheet2 A1 in Sheet 1 and return 3 below it)

So if I type "customer" in Sheet 2 A1 I get the list of Mickey, Donald, Daisey in A2 - A4

And if I type "product" in Sheet 2 A1 I get the list of Apple, Orange, Banana in A2 - A4

2. ## Re: A formula to return a list

Hi

That is a simple combination of the Match and Index Functions.

Formula For A2

=INDEX(Sheet1!\$A\$1:\$B\$4,ROW(),MATCH(\$A\$1,Sheet1!\$A\$1:\$B\$1,0))

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. ## Re: A formula to return a list

No sample needed for something so simple.

Sheet2 B2, then drag down.

=IFERROR(INDEX(Sheet1!\$A\$2:\$B\$4,ROWS(\$A\$2:\$A2),MATCH(\$A\$1:Sheet1!\$A\$1:\$B\$1)),"")

If you still need it for 2003 then just replace IFERROR with =IF(ISERROR(formula),"",formula) as you have previously.

4. ## Re: A formula to return a list

LOL

I was dropping a hint.

5. ## Re: A formula to return a list

Hey Guys,

Thanks for the help. I'm not great on this if error and match stuff. I was just about to post a sample until I read Jasons post. I am trying to manipulate it for 2003 but I'm doing something wrong.I'm assuming this will be array?

Please Login or Register  to view this content.

6. ## Re: A formula to return a list

This is the code I used for reference to try do yours. Is there a way to tweak this code to ignore ""

Please Login or Register  to view this content.

7. ## Re: A formula to return a list

I had a typo, so that didn't help.

It's only the selection of Customers / Products that could return an error, so creating a data validation list for that will omit the need for error trapping the formula. (Hopefully 2003 has validation, I can't remember that far back ).

=T(INDEX('SH1 BEFORE'!\$A\$2:\$B\$5,ROWS(\$A\$2:\$A2),MATCH(\$A\$1,'SH1 BEFORE'!\$A\$1:\$B\$1,0)))

8. ## Re: A formula to return a list

Thanks Jason I appreciate that.

That worked in 2003. You have been a great help (Again)

Do you know how I can get this to ignore blanks. This creates a list but because of the source it always starts with a blank. The Mega formula you did for me previously fixed that problem before however I don't need the mega formula for this and I wouldn't now where to start altering the mega formula to suit this
Please Login or Register  to view this content.

9. ## Re: A formula to return a list

See if this works

=IF(ISERROR(INDEX(AN\$3:AN\$1500,MATCH(0,COUNTIFS(BM\$2:BM2,AN\$3:AN\$1500,BM\$2:BM2,"<>"),0))),"",INDEX(AN\$3:AN\$1500,MATCH(0,COUNTIFS(BM\$2:BM2,AN\$3:AN\$1500,BM\$2:BM2,"<>"),0)))

10. ## Re: A formula to return a list

Hi Jason,

That didn't work.

This is the formula I have in AN
Please Login or Register  to view this content.
This is creating a list down AN and generally the fist entry on the list will be "" because the the first line is the opening balance and there is no category to it.

Then in BM I have
Please Login or Register  to view this content.
which is condensing the list of AN. It eliminates all duplicates and mid blanks but not the first blank.

11. ## Re: A formula to return a list

What do you have in BM1 and BM2?

To skip the first blank, you need a blank at the top of BM so that it is included in the COUNTIF range.

12. ## Re: A formula to return a list

BM1 is empty
BM2 is
Please Login or Register  to view this content.
This is the heading In this case it's Fundraising

13. ## Re: A formula to return a list

In that case, expanding the countif range to BM1 should fix it

=IF(ISERROR(INDEX(AN\$3:AN\$1500,MATCH(0,COUNTIF(BM\$1:BM2,AN\$3:AN\$1500),0))),"",INDEX(AN\$3:AN\$1500,MATCH(0,COUNTIF(BM\$1:BM2,AN\$3:AN\$1500),0)))

As before, you might need ="" in BM1

14. ## Re: A formula to return a list

Again,

Spot on. Worked perfect. Great to have it neat and tidy. Thanks so much for the help. How much to stick a USB key in your ear and download

15. ## Re: A formula to return a list

Originally Posted by Nitro2481
How much to stick a USB key in your ear and download
Not recommended, too much corrupt data. In serious need of error check and defragmentation

##### 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