Search for single value in table array

[andrewv19]

Hey all,

This is a question I've had for a while, and researched many times without finding a solution (I hope I'm not just missing something very simple). My goal is to lookup a value in a table I have. Specifically, I'm trying to search multiple columns for a value, and return a value from the same row.

Here's an easy example of what I am looking for (see attached): organizing a camping trip. I have a list of cabins, and assigned the participants to each one, based on how many bunks were available in each cabin. I'd like to turn this list into a rooming list that I can sort alphabetically by participant. I'd like to do this by using a search function to lookup which cabin the person was assigned.

ExcelHelp_Table_and_Desired_Results.xlsx

Please help! Many thanks.

[oeldere]

I rearange the data with the macro below.

After that you can just filter on column D.

See the attached file.

Please Login or Register  to view this content.

[daffodil11]

If VB isn't your style you could try this. It's a bit long though.

Please Login or Register  to view this content.

[XOR LX]

Or this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in B16 and copy down:

=INDEX($A$4:$A$8,MATCH(A16,INDEX($C$4:$F$8,,MATCH(1,MMULT(TRANSPOSE(ROW($A$4:$A$8))^0,--($C$4:$F$8=A16)),0)),0))

Regards

[andrewv19]

Thank you to all of you. each of those solutions works. I'm going to end up going with the Macro, only because my actual use requires a much larger data set (63 R x 7 C) and the macro can cut through it faster. Thanks all!

[oeldere]

Thanks for the reply.

Glad I could help.

You can add point to the rep(utation) of the members who helped you, by clicking on the star on the left side.

[XOR LX]

Bah! Re-arranging the data's cheating!

[Tony Valko]

Here's another one...

This array formula** entered in B16 and copied down:

=INDEX(A:A,SMALL(IF(C$4:F$8=A16,ROW(C$4:F$8)),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.