Lookup & Vlookup help

[Dan Wilson]

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...

 

[Aladin Akyurek]

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?

 

[Dan Wilson]

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...

 

[Aladin Akyurek]

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.

 

[Dan Wilson]

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...

 

[Aladin Akyurek]

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.

 

[Dan Wilson]

Thank you Aladin. That explains it very well.
Dan Wilson...

 

[Dan Wilson]

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...

 

[Aladin Akyurek]

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.