Vlookup array multiple columns

Question

Excel wiz's,

I'm trying to build a report with a simple drop down list of names. Rather than try to explain in more detail, let me give you a sample dataset:

Table1:

    Text                                Person1          Person2          Person3
    String here contains name(s)        Mike Smith       Robert Johnson   Suzy Q
    Another string with name(s)         Dan Boy          John Michael     Bob Wise
    Different string with name(s)       Robert Johnson   Suzy Q

In my report sheet, I have a drop down list of all the possible "persons" that I want to chose from and then return all values from the "Text" column in an array. I have been able to make it work with only one column using this formula, where C4 contains my choice in the dropdown list:

INDEX(Table1[#All],SMALL(IF(Table1[Person1]=$C$4,ROW(Table1[Person1])),ROW(1:1)),1)

The text column will contain all the names of the Person columns, but they are in a different case (all caps, can't change format for display purposes). Maybe a SEARCH function would be more useful? I'm not sure. I'm trying to avoid using a macro, but I am not completely opposed.

Let me know what you guys think, and thanks in advance!

Sure, Table1 will be hidden from the user. I can add whatever I need to it. — rchav9
– rchav9, Commented May 6, 2014 at 13:28

John Chrysostom · Accepted Answer · 2014-05-06 14:58:34Z

1

Simply re-organize your table so that there's one row per name... the V-Lookup on the name and get the matching list.

Person          Text                               
Mike Smith      String with names
Robert Johnson  String with names
Suzy Q          String with names
Dan Boy         Second string with names

answered May 6, 2014 at 14:58

John Chrysostom

3,9931 gold badge43 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rchav9 Over a year ago

This is what I ended up doing. I was just hoping there would be a more elegant way. Thanks everyone.

Richard Peters · Accepted Answer · 2014-05-06 13:49:32Z

0

are you trying to make validations for teams? like select team, then next drop down gives only members of that team?

you can use offset inside validation. in one cell put a validation for the list of teams. in the other cell, create a list validation, use a offset formula to return the range of members based on the selected team.

edit: not sure how to put in a table, but this is how you would fill a range with vlookup

in the table with the entries, add a column with serial number starting from 1-n
just below the drop down box, enter numbers 1 to n in order
vlookup the serial number in the table, that is the row you are looking up
for the column, use a match to look in the table which column the current selected person is
drag the formula down to fill n numbers

edited May 6, 2014 at 13:49

answered May 6, 2014 at 13:22

Richard Peters

988 bronze badges

3 Comments

rchav9 Over a year ago

I have a list of people that I want a user to choose from, then display all the results from the text column below the selection box in an array. I'm not very familiar with the uses of OFFSET. I understand what it does in terms of displacement, but how would I use it here?

Richard Peters Over a year ago

when you say array, do you mean a range of cells. like selecting the person in cell a1, then showing results from a2 til a10 one by one? or provide another drop down below to show list of choices?

rchav9 Over a year ago

A range of cells. I select my person in A1 (different sheet from table1), then display all matching cells from Table1[Text] below it in a range.

Collectives™ on Stack Overflow

Vlookup array multiple columns

Table1:

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table1:

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related