1

I have an excel spreadsheet - downloadable here contains some columns as following :

Column A : Companies' Names
Column B : Project's Name

When I try to filter my data using combo box to show only companies in specific area and use INDEX and MATCH to show all the related data in same row with the company name in the search result, since I have some companies has more than project, unlikely INDEX and MATCH just repeat first row for the company name only like the following:

Company1    Project 1
Company1    Project 1

Although company 1 has two projects 1 and 2 and must be as following

Company1   Project1
Company1   Project2

But the formula just keep repeating first row it finds.

Pic 1 - the search result which is incorrect

enter image description here

Pic 2 - the main data which I want to appear like this in the search result

enter image description here

Ram
  • 3,092
  • 10
  • 40
  • 56
A Elsheikh
  • 89
  • 1
  • 2
  • 11
  • unless you have clear example, you will not be able to get a feedback from the community. Please give a proper example with screenshot of your data, example and what you expect. – Marcel May 12 '15 at 11:47
  • please find the attached print screen, pic 1 shows the search result which is not correct , pic 2 shows the main data which has to be in the search result http://s14.postimg.org/g0jpiou9t/image.png - http://s21.postimg.org/9ehavbx5z/image.png – A Elsheikh May 12 '15 at 12:15
  • could you give an English example or translate the columns, for example, say **column R** is **company name** etc. and in your formula you are refering to **column B** that we don't know what it refers to. Your example is lacking clarity. Please clarify better adding column names, english and anything that can allow a community member to understand your problem in one look, rather than spending hours looking at what you are looking for. – Marcel May 12 '15 at 12:45
  • i converted all texts into English as a small sample and also i uploaded it on the following link: http://s000.tinyupload.com/index.php?file_id=53364233111112661814 for example if you choose spain from the combobox you will get company z twice it should be with different value but it just get first raw repeated – A Elsheikh May 12 '15 at 13:07
  • modify formula in S3 to be : =IFERROR(INDEX($C$3:$C$22,$N3,COLUMNS($R$3:R3)),"") by the way, there is a good Arabic forum for Office : http://www.officena.net/ib/index.php?showforum=135 – houssam May 12 '15 at 14:08

2 Answers2

1

As @houssam said, you can try this one in S3:

 =IFERROR(INDEX($C$3:$C$22,$N3,COLUMNS($R$3:R3)),"")

does this help?

Community
  • 1
  • 1
Marcel
  • 2,764
  • 1
  • 24
  • 40
  • thank you very much, worked perfectly, and i am already a member on officena and its real wonderful forum has so many experts i can tell. thank you – A Elsheikh May 13 '15 at 04:55
1

You are using the Company Name to match while it is not unique. MATCH always returns the first occurrence index it finds.

You can use the same formula you used to find the company name for the other columns, and that will ensure you get the same row data. Just copy the cell from Company Name to the other columns, so the formula in S3 will be:

INDEX($B$3:$K$32,$N3,COLUMNS($R$3:S3)

R3 changes to S3, and this will return the Amount column, as COLUMNS($R$3:S3) returns 2.

dePatinkin
  • 2,239
  • 1
  • 16
  • 15