[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

Vlookup

Hi,

In my report I have a list of names which I took from our remedy system. For my trouble tickets the Vlookup is fine, but my request tickets I found out the techs are typing in their own name so my vlookups do not work all the time. Is there away to fix vlookup to find Rich and add that data to Richard, or somehow just go by the last name?
0
Edward Pamias
Asked:
Edward Pamias
1 Solution
 
armchair_scouseCommented:
Without knowing the structure of your data (it would be helpful if you could post a small sample, including your VLOOKUP), iF you want the VLOOKUP to work by the lastname rather than the firstname, then the VLOOKUP can be changed to look at a different column easily enough.
If your firstname and lastname are in the same cell, then that too isn't a huge problem, the surname should be easily extractable using the MID function and something to search for the space between the first and last names, perhaps FIND, for example.

Could you also consider doing a Find and Replace to replace 'Rich' with 'Richard', then you don't have to change your VLOOKUP?  Just an idea...

Hope this helps
0
 
sbaughanCommented:
You can crate a table with the names and their different ways to be written, then find the corresponding complete first or last name with a DGET function, in the help on the function, they even explain how to set multiple criteria if needed.
0
 
Edward PamiasAuthor Commented:
Here is the sample sheet. Daily Trend has the names I need to look up in the IMAC Report. Since the techs type their names in themselves my vlookups are messed up. Ignore the incident column because that is fine. I need to modify my vlookups to ignore any errors that the techs may have made in typing their names.
samplesheet.xlsx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NBVCCommented:
Do you have specific samples you can point us to in the attachment that you think should have been a match but currently are not?
0
 
Edward PamiasAuthor Commented:
If you compare the names between Daily Trend and IMAC Report... some of the names are off ...either by a space or spelled incorrectly. Go to line 28 on the daily trend, change Richard to Rich and all the numbers will show up as zero. See if you can modify the lookup to find his data.
0
 
NBVCCommented:
You can try either of these:

=IFERROR(VLOOKUP(LEFT($A4)&"*"&MID($A4,FIND(" ",$A4)+2,100),IMACReport!$A$5:$G$38,2,FALSE),0)

or

=IFERROR(VLOOKUP("*"&MID($A4,FIND(" ",$A4)+2,100),IMACReport!$A$5:$G$38,2,FALSE),0)

the first looks up names that have the same first letter of first name, and complete last name.

the second only matches the last name (but I am not sure if you will have several people that may have same last names... the first formula narrows it down a bit more.. but is not 100% because you could also potentially have 2 different R Livingston people.
0
 
Edward PamiasAuthor Commented:
Rich Livingston is on line 28.... will this work?
0
 
Edward PamiasAuthor Commented:
I was looking at the wrong thing.... I will test it.
0
 
Edward PamiasAuthor Commented:
I tried it I am coming up with all zeros.
0
 
NBVCCommented:
The formula I gave you goes in C4, then copied down.

You will need to adjust the column_index for the other IMAC columns...
0
 
NBVCCommented:
Here is a your workbook back, updated with the formula.
Copy-of-samplesheet-2.xlsx
0
 
Edward PamiasAuthor Commented:
This worked great thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now