Index match, Choosing between multiple returns

December 10, 2017 at 03:36:53
Specs: Macintosh
I am trying to set up a system that allows me to type someones surname into a cell in order to return all of their details from a database in the next few cells, using Google Sheets. Currently having a problem with those with the same surname but different first name. E.g. John Smith and Jane Smith, currently the formula will only return Jane being the alphabetical first. Preferably I would like a drop down option for the choice but I have no idea how to do that. Please could anyone help?

See More: Index match, Choosing between multiple returns

Report •

#1
December 10, 2017 at 06:46:27
If your only question is how to create a drop down list in a cell, you should be able to follow the instructions found via a simple Google search:

create drop down list in google sheets

That said, we don't know what your formulas or data looks like, so if your question involves more than the creation of the drop down, please provide some more details. If you need to post example data to help clarify your requirements, please click on the How-To link at the bottom of this post and read those instructions before posting.

I'll toss out one suggestion that may not apply since I don't know anything about your data.

If the issue is that you have first and last names in separate cells, you can combine the names in a "Helper Column" and than use that column as the search column:

e.g. Starting with this...

       A           B      
1     John       Smith
2     Tom        Smith

...do something like this:

         A          B           C
1   =B1&" "&C1     John       Smith
2   =B2&" "&C2     Jane       Smith

Now you can do a VLOOKUP on Column A and find the full name. This can get complicated for names like John Thomas Smith so the actual solution will need to be based on your specific data.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#2
December 11, 2017 at 01:57:00
    A       B        C
1  39144  Alex    Anderson
2  39713  Olivia  Anderson

Database looks like this

This is my current formula allowing me to type just the surname of the person and return their first name, however it will not allow me to choose between those two names. Also the id number in column A can't be used for this process and they are not known before the names appear.

=iferror(INDEX(Database!$B$1:$B,Match($AE3,Database!$C$1:$C)))

What do I add to this in order for it to differentiate between the two first names?

message edited by ADunbar


Report •

#3
December 11, 2017 at 03:59:48
I don't think there is anything you can "add" to your formula to differentiate between multiple first names. How would the formula know which first name the user wants? Unfortunately, Google Sheets cannot read minds - yet. ;-)

Why not create a drop down of full names a chose the entire name from the list? In other words, eliminate the manual entry.

If you want to be able to choose a first name once the last name I entered, you'll need some type of list to choose from anyway, so why not combine it all into a single step?

Creating the first name list based on the last name entered is going to require some VBA Code (a macro) which makes it even more complicated. It can be done, but it'll take a little work. Let me know if that is necessary.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Related Solutions

#4
December 11, 2017 at 13:02:38
BTW...is that really your formula? I don' know much about Google sheets, but that formula won't work in Excel.

$B1:$B and $C1:$C are not valid ranges. Excel requires a row number after the column letter.

In addition, the IFERROR function is missing the value_if_error argument. Excel complains about that because it doesn't know what to return if there is an error.

When posting a formula in this forum, it's best to copy and paste the actual formula (assuming it is working) as opposed to manually typing it into a post.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Ask Question