Automatically list values using INDEX and MATCH

[Rob-V-1990]

Hi all,

First of all, I'm new here but your forum has helped me a lot in the past.
Now, I'm encountering the following problem and am not able to solve it myself.

I have created a sales pipeline list in a workbook. One of the sheets ("Contacted potentials") has the following setup:

A---------------------B---------------C----------------D
Company name--------Location--------Probability--------Total company sales

Rows 1, 2 and 3 have other information. The actual list starts at the 4th row.

In another worksheet ("Overview"), I want to create a list of companies that have agreed on working with us (displayed by a probability of 100%).
This should preferably be done in column A. Furthermore, column B should list the total company sales for that specific company.
I have tried this myself using INDEX and MATCH formulas, by using the following formula:

=INDEX('Contacted potentials'!$A$4:$A$1000,MATCH(100%,'Contacted potentials'!$C$4:$C$1000,0))

But this only displays one of the companies, not all those which have a probability of 100%.
I am hoping you can help me out on this one.

Thanks in advance,

Rob


Ps. it is possible to automatically update this list (by pressing F9) when a company's status changes to 100%?

[Tony Valko]

See if you can adapt this to your application:

http://www.excelforum.com/tips-and-t...ml#post3376007

[Rob-V-1990]

I've just tried to do so by using the following formula:

=IFERROR(INDEX('Contacted potentials'!$A$4:$A$1000, SMALL(IF('Contacted potentials'!$C$4:$C$1000=100%, ROW('Contacted potentials'!$C$4:$C$1000)), ROWS(A$2:A2))), "")

However, strangely enough, now it lists some of the companies that do not have 100% probability and does not list some of them that do..

[Tony Valko]

Index the ENTIRE column otherwise you have to use an "offset correction":

=IFERROR(INDEX('Contacted potentials'!$A:$A,SMALL(IF('Contacted potentials'!$C$4:$C$1000=100%, ROW('Contacted potentials'!$C$4:$C$1000)),ROWS(A$2:A2))),"")

[Rob-V-1990]

Works fine now! Thanks for the help and useful tips!

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.