I am trying to search a matching value over multiple rows and columns.
I want to search for a value entered in Column Q from selection B3:B102, C3:C102, D3:D102, E3:E102, F3:102, then when a match is made in a row return the text that is in Column G in the same row to Column P
Could you please add a few rows of expected results manually and post again? Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Updated example with expected results
There may be smarter ways of doing this (my formulas tend to be somewhat cumbersome) but this approach does work. This would be in Cell P8 in your sample, searching for the 333 in Q8:
Hope that helps!
Please Login or Register to view this content.
Last edited by nevi; 07-26-2019 at 04:15 PM.
Another option, put this on P3 and ENTER as array formulas, press all together CTRL+SHIFT+ENTER button, then copied down as necessary
=IFERROR(INDEX($G$3:$G$102,SMALL(IF($B$3:$F$102=$Q3,ROW($G$3:$G$102)-ROW($G$3)+1),COUNTIF($Q$3:Q3,Q3))),"")
=IFERROR(INDEX($G$3:$G$102,SMALL(IF($B$3:$F$102=$Q3,ROW($G$3:$G$102)-ROW($G$3)+1),COUNTIF($Q$3:Q3,Q3))),"")
This is the solution I ended up using and it works great. However now I have a secondary use for the same formula.
If there are 2 or more numbers the same within the $B$3:$F$102 area then only the highest in the column is returned and the rest return blanks. Is there a way to allow multiples?
Thanks
It may help us understand this additional request if you could upload another file that illustrates the issue of "2 or more numbers...". Please include expected results so that we will have something against which to test formulas/code.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks