Same Formula, Same Input, Different Results - VLOOKUP with nested IF referencing two columns

Copper Contributor

I use excel in conjunction with a barcode scanner to create attendance sheets that have sign in / sign out data for live classes. In my excel file, I work with two sheets: Attendee Information and Sign In/Sign Out (for scanning barcodes).

The first sheet is my Attendee information to be used as a reference for when we scan their barcode on the other sheet. I call this sheet "Names and Numbers"; it has Individual's Barcode Number, First Name, Middle Initial, Last Name, Email, etc.

Other than the barcode number, the attendee information is taken directly from an event report on ConstantContact. ConstantContact unfortunately creates two separate columns for 'Middle Initial' and two separate columns for 'Email' (one column for the main attendee and the other for their guests). In the past I would just copy over the information on the guest column of each into the main column counterpart. However, I wanted to streamline the process so that I don't have to do this each and every time.

Thus, I figured making a formula on my Sign In/Sign Out sheet [for the Middle Initial column (Column F) and Email Address column (Column H)] that would look at the information in the main column, and if it was blank, look at the information in the guest column and if it wasn't blank, use the main column. The main column is to the right of the guest column, btw, so I don't have to deal with LEFT in this scenario.

 

The formula I came up with is:

=IF(B2="","",VLOOKUP(B2,'Names and Numbers'!$A$2:$Z$999,IF('Names and Numbers'!$C:$C="",6,3),FALSE))

This is an adapted basic VLOOKUP formula where the col_index_num entry uses an IF formula.

col_index_num:

If 'Names and Numbers' column C (main Middle Initial column) is empty [Logical test], then use value in column 6 (guest Middle Initial column) [value if true], otherwise use value in column 3 (main Middle Initial column) [value if false].

 

[Also, =IF(B2="","",VLOOKUP(B2,'Names and Numbers'!$A$2:$Z$999,IF('Names and Numbers'!$C$2:$C$999="",6,3),FALSE)) gives the same results]

 

I was hopeful that this would solve my problem, but it seems to be having some issues being consistent.

When I input the example barcode several times, the formula either gives the proper result or 0. I've created new sheets to test it out, and it keeps giving me 0 for rows 2, 4, 14, 16-17, 20-22, 25, 27, 29. [Probably more, but I've only done the barcode entry up to row 30]

 

Does anyone know why this is happening and how to fix it?

8 Replies
I'm moving your question to the Excel community (the appropriate place for Excel questions for the future) for better visibility.
The formula in F2 is:
=IF(B2=“”,””,
INDEX((‘Names and Numbers’!$C$2:$C$999,
‘Names and Numbers’!$F$2:$F$999),
MATCH(B2,’Names and Numbers’!$A$2:$A$999,0),
SUMPRODUCT((‘Names and Numbers’!$A$2:$A$999=B2)+
(‘Names and Numbers’!$C$2:$C$999=“”))))

@Twifoo 

The result is a #REF! error.

I can kind of understand what your formula is saying, but I'll read up on INDEX, MATCH and SUMPRODUCT to get a better idea. I hadn't considered those types of formulas.

@RyleeEman 

Solution found! See my post on the Excel subReddit

https://www.reddit.com/r/excel/comments/b34r2w/same_formula_same_input_different_results_vlookup/

 

jonesin4adoob suggested:

"The problem is with your if statement that references the entire column C. Unless I’m mistaken, you’re looking to see if that specific name in the names and numbers sheet has a value in column C. If true then return 6 else 3. If that’s the case, change your if statement to something like if( VLookup (B2, names and numbers A:C, 3,0)=“”, rest of formula here...."

 

So now the full formula is

=IF(B2="","",VLOOKUP(B2,'Names and Numbers'!$A$2:$Z$999,IF(VLOOKUP(B2,'Names and Numbers'!$A:$C,3,0)="",6,3),FALSE))

I inadvertently omitted 1 argument from the formula. Therefore, allow me to modify the formula this way:
=IF(B2="","",
INDEX(('Names and Numbers'!$C$2:$C$999,'Names and Numbers'!$F$2:$F$999),
MATCH(B2,'Names and Numbers'!$A$2:$A$999,0),1,
SUMPRODUCT(1+('Names and Numbers'!$C$2:$C$999=""))))
I bet that the foregoing formula is faster than 2 IF-VLOOKUPs. If there is an additional Lookup Column, such column has to be added to the reference argument of INDEX and another comparative array has to be added to the array1 argument of SUMPRODUCT.
Conversely, another IF-VLOOKUP combination would have to be added, if the VLOOKUP formula would be used instead of the elegant reference form of INDEX.

@Twifoo 

Thank you very much for trying to help me

The formula you suggested still gives results in #REF! .

 

However, I'll keep looking into INDEX and MATCH, since Microsoft Excel also recommends using them for more specific complex formulas needs versus VLOOKUP which seems more basic. I'll probably need it for another formula that will be taking the times inputted when people scan their barcodes, and organize a clean time in and time out on another sheet. 

 

However, the solution for this issue (if C is empty, the use F, otherwise use C) can still be achieved using VLOOKUP and IF; I posted it in this conversation yesterday.

@RyleeEman 

 

For those who are also making attendance record using Excel and barcode scanning for signing in and signing out, attached is my file of all formulas used for "For Barcode Scanning" sheet (data being inputted) and "Attendance Sheet" sheet (data being organized)