• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

If Match, then VLookup|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / If Match, then VLookup|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…If Match, then VLookup
sp_PrintTopic sp_TopicIcon
If Match, then VLookup
Avatar
Danielle O

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 11, 2019
sp_UserOfflineSmall Offline
1
January 11, 2019 - 2:13 am
sp_Permalink sp_Print

Hello,

I am trying to create a vlookup for employee data, based on a number code match using the employee # as the lookup value. Unfortunately, my data has multiple codes for the same employee number, and isn't populating the lookup correctly. 

 

Here are my formulas in separate columns on sheet 2. B4 is on sheet 2 and is the employee # being matched to Sheet 1 column D

=IFERROR(IF('Sheet 1'!A:A=7700,VLOOKUP($B4,'Sheet 1'!$D:$H,3,FALSE),""),"")

=IFERROR(IF('Sheet 1'!A:A=7701,VLOOKUP($B4,'Sheet 1'!$D:$H,3,FALSE),""),"")

=IFERROR(IF('Sheet 1'!A:A=7702,VLOOKUP($B4,'Sheet 1'!$D:$H,3,FALSE),""),"")

=IFERROR(IF('Sheet 1'!A:A=7704,VLOOKUP($B4,'Sheet 1'!$D:$H,3,FALSE),""),"")

 

The data below is from Sheet 1. 

A

Deduction

B

Deduction Desc

C

Employee Name

D

Employee #

E

Org Code

F

Employee Amount

G

Employer Amount

H

Employee Gross

7700 MPERS TEACHERS EMPLOYEE LV 100012 251090 218.02 113.14 2,850.00
7700 MPERS TEACHERS EMPLOYEE BG 100015 110013 418.12 216.98 5,465.68
7700 MPERS TEACHERS EMPLOYEE CL 100059 240009 254.75 132.20 3,330.00
7700 MPERS TEACHERS EMPLOYEE LL 100069 222030 207.70 107.80 2,715.00
7700 MPERS TEACHERS EMPLOYEE DC 100078 240039 232.56 120.68 3,040.00
7700 MPERS TEACHERS EMPLOYEE AL 100081 112010 373.56 193.86 4,883.08
7700 MPERS TEACHERS EMPLOYEE HA 100093 110007 400.62 207.90 5,236.92
7700 MPERS TEACHERS EMPLOYEE AJ 100123 110013 373.56 193.86 4,883.08
7700 MPERS TEACHERS EMPLOYEE CM 100139 251090 420.40 218.16 5,495.32
7701 MPERS TCH FED FUNDED EE/ER ND 100195 2055080 400.62 819.58 5,236.92
7704 OFFICE OF STATE EMP HLTH ND 100195 2055080 0.00 207.90 5,236.92
7700 MPERS TEACHERS EMPLOYEE MM 100254 110009 0.00 0.00 0.00
7701 MPERS TCH FED FUNDED EE/ER MM 100254 110009 136.55 279.35 1,785.00
7704 OFFICE OF STATE EMP HLTH MM 100254 110009 0.00 63.72 1,785.00
7700 MPERS TEACHERS EMPLOYEE CJ 100282 210011 412.10 213.86 5,386.92
7701 MPERS TCH FED FUNDED EE/ER CJ 100282 210011 0.00 0.00 0.00
7704 OFFICE OF STATE EMP HLTH CJ 100282 210011 0.00 0.00 0.00
7700 MPERS TEACHERS EMPLOYEE ML 100296 250090 332.80 172.72 4,350.40
7700 MPERS TEACHERS EMPLOYEE MCM 100384 120030 453.89 235.55 5,933.08
7701 MPERS TCH FED FUNDED EE/ER MCM 100384 120030 0.00 0.00 0.00
7704 OFFICE OF STATE EMP HLTH MCM 100384 120030 0.00 0.00 0.00
7700 MPERS TEACHERS EMPLOYEE MCM 100400 120030 400.62 207.90 5,236.92
sp_AnswersTopicSeeAnswer See Answer
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
January 11, 2019 - 6:49 am
sp_Permalink sp_Print

Hello Daneille,

Please take a look at this nice blog post, you will find your answer there.

https://www.myonlinetraininghu.....le-columns

In regard to the linked article, I find the Index & Match solution presented at the end easier.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
3
January 11, 2019 - 11:42 am
sp_Permalink sp_Print sp_EditHistory

Hi Danielle

Are you trying to extract multiple records for the same employee number?

e.g. Employee#100282 will display 3 records as per your example above.

You can take a look at the article here : https://www.myonlinetraininghu.....le-matches

You can also consider using a PivotTable.

If the suggestions of Anders and I are not what you wanted then please attach your file with the expected result.

It will help us understand your needs better.

Sunny

Avatar
Danielle O

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 11, 2019
sp_UserOfflineSmall Offline
4
January 11, 2019 - 11:31 pm
sp_Permalink sp_Print

Unfortunately, I cannot attach the spreadsheet, as it has sensitive information. 

I cannot use a pivot table for this information, as I need it to feed into my master sheet. 

I was unable to get an index match formula to work, and would like some help. The issue I'm running into, is that the formula is finding the employee number for 7700 only, and will not populate for 7701, 7702 or 7704. 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
5
January 12, 2019 - 8:25 am
sp_Permalink sp_Print

Hello,

Please see attached example.

sp_AnswersTopicAnswer
Answers Post
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
January 12, 2019 - 11:07 am
sp_Permalink sp_Print

Hi Danielle

If you are not able to supply the file them maybe you can supply the output format based on your sample data above.

At the moment we can only make a guess what is would look like and if it is not correct a lot of effort will go to waste.

The actual solution will depend on your final output format.

Let us know what version of Excel you are using as certain Functions may not available in all versions.

Sunny

Avatar
Danielle O

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 11, 2019
sp_UserOfflineSmall Offline
7
January 18, 2019 - 6:22 am
sp_Permalink sp_Print

Amazingly Anders, that worked! thank you, thank you! Amazing! 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
8
January 18, 2019 - 4:41 pm
sp_Permalink sp_Print

Hello Danielle,

You are welcome.

What I find amazing is all the knowledge stored and made available here at MOTH. The blog articles contains lots of helpful information.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Chandler Davis
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27210

 

Member Stats:
Guest Posters: 49
Members: 31878
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.