- 541K Members
- 5,163 Online
- 644K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Same Formula, Same Input, Different Results - VLOOKUP with nested IF referencing two columns

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Same Formula, Same Input, Different Results - VLOOKUP with nested IF referencing two columns

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2019 04:59 PM - edited 03-20-2019 01:01 PM

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?

Labels:

8 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2019 03:50 AM

I'm moving your question to the Excel community (the appropriate place for Excel questions for the future) for better visibility.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2019 07:13 AM

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=“”))))

=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=“”))))

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2019 12:10 PM

Thank you very much!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2019 01:00 PM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2019 01:24 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2019 09:06 PM

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.

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-22-2019 11:34 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-23-2019 03:59 PM

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)

Related Conversations

Restrict Edit access on columns on user/group basis.

Vimmi Rawat
in
SharePoint
on
02-04-2020
67
Views

0 Likes

0 Replies

Pivot Table StDev calculates different value then the STDEV formula

zsoltturkosi
in
Excel
on
10-21-2019
199
Views

0 Likes

4 Replies

need macro to find and copy range of data between specified cells.

spike3rd
in
Office 365
on
10-09-2019
150
Views

0 Likes

0 Replies

Deleted questions in a copied form still showing in results

ThomasMiedema
in
Microsoft Forms
on
10-07-2019
37
Views

0 Likes

0 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft