Excel Gurus: Cotton needs help again!

[Charles L. Cotton]

Okay, you guys bailed me out of a bind earlier, so I'm back for more help.

I use an online registration system for our monthly IDPA matches. The match is growing dramatically and I need to streamline my squadding procedures.

Here's my system. I export the online database as a CSV file then import it into an Excel file with 3 worksheets. One sheet has the seven squads into which I assign each person registered for the match. The second sheet is used to create name badges using Word merge print. This page takes the name and squad number from the first sheet where I have assigned people to squads. The third sheet is simply an empty sheet into which I import the CSV file I previously exported from the website.

The third sheet with the imported data contains the person's first and last names (different fields), PSC membership status, whether they are staff, and other information. I need to add some of this information to the sheet from which the badges are printed. In order to do this, I currently have to manually add the information to the "badge sheet," but I hope this can be streamlined.

Can Excel look up a person's name on Sheet 2, then select information pertaining to that person in a Sheet 3 (ex. PSC member status) and put it in a cell on Sheet 2 that will be used to create name badges? To complicate matters, the names on Sheet 2are actually cell reference2 to Sheet 1. I would think that a LOOKUP function would accomplish this, but I can't seem to get the right syntax.

Yeah, I know.
Chas.

[SC1903A3]

Google Vlookup for excel. That should do what you need it to do.

[Middle Age Russ]

A couple of lookup functions seem to be called for. The syntax can indeed be an issue, as can certain caveats about how lookup functions work. I would suggest using a nested function like =if(MATCH(lookup_value,lookup_array,match_type)<>0,VLOOKUP(lookup_value,lookup_array,column),""). This uses the MATCH function to test for an exact match and if it finds one returns the results of the VLOOKUP function. Otherwise it leaves the cell contents blank. This might work as long as the data in the first column of the lookup array for each function is sorted in ascending order. If it is not sorted the results will be consistently wrong due to how the functions work.

Based on your description, you may need to create a column for worsheet 3 and possibly worksheet 2 that contain the concatenated name (=Last_Name&", "&First_Name) so that the lists can be sorted and the lookup funcitons can work as you want them to. Note also that the lookup_value must be in the first column of the lookup_array -- if it is in another column instead the function will not work.

If you continue to have issues, I'd be happy to look at the workbook as is and see if I can't help you out. Feel free to email me a copy if you'd like. I get the feeling that you are almost there with it.

Russ

[Charles L. Cotton]

Thanks guys.

I left out a key fact. I need to combine an If/Then function with the lookup. What I need returned will depend upon content of the cell that's "looked up." Is it possible to combine vlookup and if/then functions?

Chas.

[Middle Age Russ]

Sure, you can combine functions with IF() functions and also use nested If() functions. You may be limited as to the number of nested IF() statements. I think Excel used to limit you to seven nested IF() functions, but don't know if that is still the same limit. I have never run up against a need to nest even seven IF() statements.

[Charles L. Cotton]

I think I found a way to do what I need. It's not as fast as what I had hoped, but much faster than I'm doing now.

Thanks again for your help.

Chas.

[Jumping Frog]

Can Excel look up a person's name on Sheet 2, then select information pertaining to that person in a Sheet 3 (ex. PSC member status) and put it in a cell on Sheet 2 that will be used to create name badges? To complicate matters, the names on Sheet 2are actually cell reference2 to Sheet 1. . . . .. I need to combine an If/Then function with the lookup. What I need returned will depend upon content of the cell that's "looked up." Is it possible to combine vlookup and if/then functions?

Yes.

If you give some actual sheet names and cell addresses, I'll help with the actual formula.

The issue with using vlookup and other functions is you'll get a "#N/A" error if the vlookup doesn't find a value. So I test for error status first, and then use the logic.

First, if you have PSC member numbers, that is a better way of assuring a match than a name, as there are more potentially more than one shooter named "John Smith". But I will assume you have unique names in sheet "Badges" and the same formatted names in sheet "Memberlist". Lets assume the Names are in Column A in both sheets and "Membertype" is in Column E. Assume "Membertype" has values like "Member", "Club Officer", "RSO". Assume there are 1000 rows in the Memberlist sheet.

If the Memberlist lookup fails, then consider the badge type to be "Guest".

Here is how it would look:

=if(not(iserror(vlookup(Badges!A2,Memberlist!$A$2:$E$1000,1,FALSE))), vlookup(Badges!A2,Memberlist!$A$2:$E$1000,5,FALSE), "Guest")

In pseudo code, it is: If looking up the name in memberlist is not an error, then the true value is the corresponding value in column 5 (E). The false value is "Guest".

[jimlongley]

Can Excel look up a person's name on Sheet 2, then select information pertaining to that person in a Sheet 3 (ex. PSC member status) and put it in a cell on Sheet 2 that will be used to create name badges? To complicate matters, the names on Sheet 2are actually cell reference2 to Sheet 1. . . . .. I need to combine an If/Then function with the lookup. What I need returned will depend upon content of the cell that's "looked up." Is it possible to combine vlookup and if/then functions?

Yes.

If you give some actual sheet names and cell addresses, I'll help with the actual formula.

The issue with using vlookup and other functions is you'll get a "#N/A" error if the vlookup doesn't find a value. So I test for error status first, and then use the logic.

First, if you have PSC member numbers, that is a better way of assuring a match than a name, as there are more potentially more than one shooter named "John Smith". But I will assume you have unique names in sheet "Badges" and the same formatted names in sheet "Memberlist". Lets assume the Names are in Column A in both sheets and "Membertype" is in Column E. Assume "Membertype" has values like "Member", "Club Officer", "RSO". Assume there are 1000 rows in the Memberlist sheet.

If the Memberlist lookup fails, then consider the badge type to be "Guest".

Here is how it would look:

=if(not(iserror(vlookup(Badges!A2,Memberlist!$A$2:$E$1000,1,FALSE))), vlookup(Badges!A2,Memberlist!$A$2:$E$1000,5,FALSE), "Guest")

In pseudo code, it is: If looking up the name in memberlist is not an error, then the true value is the corresponding value in column 5 (E). The false value is "Guest".

Bob, do you have any relatives in upstate NY? You talk just like the young lady that taught me Excel, in 1993.