Non-exact Vlookup with multiple search criteria - Microsoft Excel

0 votes

I'm having trouble using Excel to retrieve some dates from a table. The following figure illustrates the format of the data:

Name---------------------------First Name------------------- Surname
Billy Peter Hunt---------------Tim---------------------------Smith
Chris Paul Smith---------------Brad--------------------------Johnson
Dave Colin Jones---------------Karen-------------------------Perry
Dr James Clyde Watt------------Dave--------------------------Jones
John Phil Cross----------------Chris-------------------------Smith
Kelly Holmes-------------------Hector------------------------Baxter
Norman Bryce-West--------------Billy-------------------------Hunt

I want to be able to search through the Name column for the first cell that has the individual's First Name AND Surname and output the results into a new column for each person listed in the First Name and Surname columns (such as "Tim Smith," "Brad Johnson," etc.).

I am unable to do an exact match search because there are numerous middle names, titles, and double-barreled surnames in the Name column. To discover if there are any sub-strings that match, I must scan through each cell's contents.

I've discovered that I can search using either the First Name OR the Surname columns using Vlookup with wildcards, but I'm unable to figure out how to use both columns simultaneously.

Any assistance would be greatly welcomed in this. I've been putting all kinds of things into Google and yelling in frustration a lot.

Dec 22, 2022 in Others by Kithuzzz
• 38,000 points
414 views

1 answer to this question.

0 votes

Use the array function:

=INDEX(A$2:A$100,MATCH(9^99,SEARCH(B2,A$2:A$100)*SEARCH(C2,A$2:A$100)))

This will work with Ctrl+Shift+Enter but not with Enter alone.  

Depending on how many rows you have, replace 100 to any other amount you require (or, for the entire column, change A$2:A$100 to A:A, but this may take longer).

Keep in mind that if there are multiple people with the same name and last name (e.g. Tim Smith and Smith Tim, the function will give you the last result from the column).

answered Dec 23, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
0 answers

Excel: Highlighting duplicates with exact matches

1 I've got two columns of data, and ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
472 views
0 votes
1 answer

How search data in excel with openpyxl?

The method iter_rows in the library has ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,600 points
7,173 views
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,600 points
754 views
0 votes
1 answer

Repeated excel rows based on a cell with multiple values

You can use this query: let ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 63,600 points
1,543 views
0 votes
1 answer

Using Excel VLOOKUP() function across two sheets

The syntax for VLOOKUP is VLOOKUP(Lookup_Value,Table Array,Col_index_num,Range_lookup) OR, to start in ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 63,600 points
1,004 views
0 votes
1 answer

Convert three letter country codes to full country names

Just create a list to be used in ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,600 points
1,100 views
0 votes
1 answer

How to categorize/classify numbers from different ranges using Excel?

I wouldn't advise utilizing nested IFs because ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,600 points
3,558 views
0 votes
1 answer

Using VLOOKUP()

Vlookup takes the lookup value first, so ...READ MORE

answered Nov 5, 2022 in Others by narikkadan
• 63,600 points
530 views
0 votes
1 answer

Hosting Microsoft Edge via WebView2Loader.dll with Excel VBA

Here's an API to communicate with Chromium ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,600 points
2,834 views
0 votes
1 answer

Is there a way to SUMIFS with multiple criteria + true or false?

You can add this as the  last criteria: =SUMIFS(STORE!$C$6:$C$1000;STORE!$A$6:$A$1000;""&SUMMARY!$D$5&"";STORE!$D$6:$D$1000;""&SUMMARY!$C$9&"";STORE!$E$6:$E$1000;""&SUMMARY!D8&"";STORE!$AA$6:$AA$1000;TRUE) STORE!$AA$6:$AA$1000: ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 63,600 points
1,097 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP