Retrieve different values from same index+match findings

[jmmdic]

Hello,
I would like to retrieve different values that correspond to the same findings. Example: Imagine building 1 where the same tenant is in different floors: tenant A, for example, is in floor 5 and 2. Through index+match, it will only retrieve the first number, which is floor 5. Is it possible to retrieve all the floors in which it is?

thank you in advance,
joão

(excel enclosed with this example).



Sheet 1 Sheet 2
Building 1 Building 1

# Name Floor # Name Floor
1 A 5 1 A 5
2 B 4 2 B 4
3 C 3 3 C 3
3 C -1
2 B 0
1 A 2

[Glenn Kennedy]

Try this array formula, copied across and down:

=IFERROR(INDEX($J:$J,SMALL(IF($H$5:$H$10=$B5,IF($I$5:$I$10=$C5,ROW($I$5:$I$10))),COLUMNS($A:A))),"")

I am not entirely clear what the relevance of the building number is, however.


Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[jmmdic]

Thank you very much.
The point of having the building number is that I have several buildings, so i would just need to change its number and have the respective information.
Could you please clarify why do you use COLUMNS($A:A)? Should I define a column with no values?
Also I am trying to add another if related to the building, but unfortunately I'm not being able to make it work.
I would be most thankful if you could give me some light on this matter, as this would be exactly what I need!
Thank you very much,
joão

[Glenn Kennedy]

COLUMNS is just a counter. Columns($A:A) resolves to 1, when you drag it across, it becomes Columns($A:B), which resolves as 2, etc. It enables the return of multiple values, returning them in order of the rows in which the matches occur, smallest row number first, then the second, and so on.

I'm still not clear about the building number... Is the information in I9 coming from a dropdown box? Are there formulae which draw the information from elsewhere into cells H5 to J10??

[Glenn Kennedy]

...or do you have DIFFERENT lists in DIFFERENT places, one for each building? If so, then I really need to see how that is organised (post a revised sheet), 'cos it makes it much more complicated!!

[jmmdic]

Apologies, you are right - just uploaded the new book with the correct database.
Thank you

[Glenn Kennedy]

I assume (having seen this) that you also want the results concatenated into a single cell??

[jmmdic]

That would be the perfect scenario!

[Glenn Kennedy]

OK. Perfection is possible. It just takes a bit longer. You actually asked a much more complicated question than you realised. Basically you want all the parameters to update when you alter the building number AND you want the floor numbers concatenated into a single cell.

I used an array formula to update the # and names and an array-entered User Defined Function - CONCATALL - (right click on sheet name/View Code in Module 1 to see the code used) to return the results, comma separated for you.

Now change the building number on "Result" & see what happens.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

ALSO remember to enable macros when opening the file.

[jmmdic]

Thank you very very much - for the quality and quickness of the answer. It is perfect.

[Glenn Kennedy]

Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.