Forum Discussion
Solved
Using VLOOKUP over multiple columns
Pardon me if I posted this earlier. I didn't see my questions when I later returned so here it goes again. Using Microsoft 365 personal, Excel on a MacBook. I have a basis understanding what VLOOKUP is doing after countless videos. I have a roster of members (name, address, phone, emails, lodge). I got the VLOOKUP to work with the names, addresses and phone number (Columns C thru G), displaying in a new worksheet perfect. After the phone number column G, there are columns for other things ie $, parking, etc. Then in the last two columns (Q & R) I'd like some more member's information from that previous VLOOKUP. These would be columns 16 and 17. Cut and pasted the first formula. I changed the =VLOOKUP formula to reflect column 16 and 17 and I get the #REF! error message. It's sort of a auto populate problem, there I'd like to avoid typing info in columns Q and R. I'd really like someone to go live with me on this spreadsheet, where I could observe on my screen, if that is possible. Data is all fake. HELP
Perhaps you simply need to expand the lookup range.
For example, if your formula now looks like this:
=VLOOKUP(A2, 'Roster Sheet'!$A$2:$G$1000, 16, FALSE)
change that to
=VLOOKUP(A2, 'Roster Sheet'!$A$2:$Z$1000, 16, FALSE)
You'll have to modify this for your setup.
If this doesn't help:
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
See the attached version. I edited the formulas to use structured table references.
Great job! Let me run it by the Registrar who will b receiving information and inputing the various fields. I'm just assisting.
If we decide to add a column, say in the tan area, like "Guest", will the formula need to be updated in Q and R =IFERROR(VLOOKUP(B3,Table_2,7,FALSE)&"", "") and =IFERROR(VLOOKUP(B3,Table_2,8,FALSE)&"", "") because of inserting columns? Also noticed you used Table_2 in the formula, not the tab name. I guess it works OK that way.
Ken
I only now got notified of your reply.
> will the formula need to be updated?
No, probably not.
Perhaps you simply need to expand the lookup range.
For example, if your formula now looks like this:
=VLOOKUP(A2, 'Roster Sheet'!$A$2:$G$1000, 16, FALSE)
change that to
=VLOOKUP(A2, 'Roster Sheet'!$A$2:$Z$1000, 16, FALSE)
You'll have to modify this for your setup.
If this doesn't help:
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
