Lookup & Vlookup help

Dan Wilson

Well-known Member
Joined
Feb 5, 2006
Messages
504
Office Version
  1. 365
Platform
  1. Windows
Good day. I'm running Excel 2013 on Windows 10. I have a workbook that tracks events attended by members of our group. There are 150 members listed as Lastname, Firstname on the first worksheet. A second worksheet contains the names of members who have attended any of several events throughout the year. The names on the second worksheet match the spelling of the names on the first worksheet. Both worksheets are in ascending order. However, there are only 90 members on the second worksheet. Column A contains the member names on both worksheets. On the second worksheet, Column B contains the number of events attended for the year. I have tried using Indirect, Lookup and Vlookup to populate Column B on the first worksheet so that we can see who has attended events and who has not. The results are all the same. Where there is a comparison of a member who has attended an event, Column B on the first worksheet more times than not shows the correct number of events attended. Sometimes it does not. Members who did not attend any events seem to copy the number from the previous member. Any suggestions would be appreciated.

Sample formula: =VLOOKUP(A2,'Events 2016'!$A$5:$B$95,2)
=VLOOKUP(A3,'Events 2016'!$A$5:$B$95,2)

The first formula shown is in Cell B2 on the first worksheet with 150 member names. The second formula is in Cell B3. Columns A2 and A3 contain the member names and escalate down through the 150 names. Events 2016 is the second worksheet. Column A5 on the Events 2016 worksheet contains the member name. Column B5 contains the number of events attended by that member. A5:B95 contains the 90 members data. Rows 1 through 4 of the second worksheet contain other data not pertinent to this issue.
Dan Wilson...
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hard to read... Concisely, are you trying to establish whether A2 (and A3, so on) exist in A5:A95 of Events and if so, return the corresponding value from B5:B95 of Events?
 
Upvote 0
Good day Aladin. That is exactly what I want. I was trying to give enough detail to figure it out. You did it in one sentence.
Thank you, Dan...
 
Upvote 0
Good day Aladin. That is exactly what I want. I was trying to give enough detail to figure it out. You did it in one sentence.
Thank you, Dan...

Ok. Doesn't this suffice?

=VLOOKUP(A2,'Events 2016'!$A$5:$B$95,2,0)

Copied down, it should fetch the value associated with A3, and so on.
 
Upvote 0
Good day again Aladin. Adding the "0" does make it work. I still have much to learn about Excel. This forum has saved me every time. I will dig deeper into the workings of the Vlookup function to learn why the "0" made it work. Whenever there is not a match found for a member name, I get "#N/A" as a result. That I can live with as it shows me that member did not attend any events. Thank you for your excellent help.
Dan...
 
Upvote 0
Good day again Aladin. Adding the "0" does make it work. I still have much to learn about Excel. This forum has saved me every time. I will dig deeper into the workings of the Vlookup function to learn why the "0" made it work. Whenever there is not a match found for a member name, I get "#N/A" as a result. That I can live with as it shows me that member did not attend any events. Thank you for your excellent help.
Dan...

FALSE or 0 mean exact match: Looking for xad this must exist exactly as xad in the first column of 'Events 2016'!$A$5:$B$95.

Omitting this from the VLOOKUP formula or using 1 (TRUE) means approximate match: xad would match vax if xad is not available and 'Events 2016'!$A$5:$B$95 is sorted in ascending order on its first column.
 
Upvote 0
Thank you Aladin. That explains it very well.
Dan Wilson...
 
Upvote 0
Good day again Aladin. I really appreciate your follow-up on my problem. Not only is the problem solved, but I also learned how to use IFERROR to get rid of the "#N/A". I love Excel. This forum is the greatest!
Dan Wilson...
 
Upvote 0
Thank you Aladin. That explains it very well.
Dan Wilson...

You are welcome. I appear to have skipped the #N/A issue though!

Good day again Aladin. I really appreciate your follow-up on my problem. Not only is the problem solved, but I also learned how to use IFERROR to get rid of the "#N/A". I love Excel. This forum is the greatest!
Dan Wilson...

Yes, since the appearance of IFERROR in Excel, this new function is often used to trap errors like #N/A. A recent addition in 2016 Excel systems is IFNA, built-in specific to #N/A.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top