Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

multiple match return return header

0

Hi Guys, i really need some help, i cannot figure this formula out.I am trying find all matching numbers in a table and retun the headers of each matching cells as in the example below, in which i am trying to match all numbers in the referenced cell, thanks in advance.

        A       B      C       D      E     / reference cell is G1 which has number 9 in it

1     dog    cat   bird   goat   fish  / result should be something like the below in G2:G7

2      9       4       1       6       9                       dog

3      5       7       9       5       9                       fish

4      1       9       4       9       7                       bird

                                                                     fish

                                                                      cat

                                                                      goat

Sorry row numbers corrected, headers are just words in row 1

excelformulas-functionsheader-footertable
MS Office Version: 2007
posted by: pmausexcel on Wed Mar 21, 2018 at 4:46 am
Answer
Discuss

Answers

0

I placed the column captions in range A2:F2 and the numbers below that in range A3:F5. (I added the horse to have a column without a 9 in it.) Then I wrote the variable in G1 as you have suggested. Now I placed my formula in A7 and copied it from there to B7:F7

=IF(ISNA(MATCH($G$1,A$3:A$5,0)),"",A$2)

Now, as you change the referenced number in G1 you will observe different lists in row 7.

That is the easy part. Its advantage is that you can add as many columns and rows of numbers as you may desire. However, you appear to want the result arranged vertically in column G and I suspect that you will want to do away with the unavoidable blanks before you are done wishing for improvements. I estimated that a formula to do all that would be about 16 times the size of the above one which would make it about 64 times more complicated.

Therefore I added VBA to the attached workbook. You will find the code in the code module for sheet "MatchCaptions". (Press Alt+F11 to access the VB Editor.) Read the comments. There are lots of things you can modify to better match your requirements. The code runs whenever you change G1.

posted by: Variatus (rep: 4889) on Wed Mar 21, 2018 at 6:34 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login