0

Request:
Please assist w\ formula logic to provide error handling and Vlookup functionality across two possible columns (within table array) and return employee ID (third column in array).

Goal:
Create an auto-populating attendance tracker that looks up the inputted phone number and returns their employee ID on an embedded "Roster" worksheet. Also, I am trying to give functionality for vlooking up across two phone numbers.

"Attendance Tracker" worksheet Table Layout: (emphasis in italics)
DATE | TIME | CALLER ID | TYPE | REASON | DETAILS | EMPLOYEE # | ETC...

  • Caller ID = Column D
  • Employee # = Column I

"Roster" worksheet table-array for VLOOKUP

  • Column H = "Contact Primary"
  • Column I = "Contact Secondary"
  • Column J = "Employee Number"

Primary Formula(s)

  • "First"=VLOOKUP(D2,Roster!H:J,3,0) <--- Looks up emp number against "Contact Primary"
  • "Second"=VLOOKUP(D2,Roster!H:J,2,0) <--- Looks up emp number against "Contact Secondary"

My Attempt
=IF(D2="","",IF(ISERROR(IF(ISERROR(VLOOKUP(First)),VLOOKUP(Second),VLOOKUP(First))),"Add # to Roster",VLOOKUP(FIRST))))

MY RESULTS
"Caller ID" left blank - Perfect! Top level if-statement fixes blanks.
"Caller ID" exists in "Contact Primary" Column - Perfect! Value Returns.
"Caller ID" exists in "Contact Secondary" Column - Oh No! "Add # to Roster" returns.
"Caller ID" DOES NOT exist in either "Contact Primary/Secondary" - Oh No! "Add # to Roster" returns.

I hope I explained that well enough. And thank you so much in advance if you are able to assist!

Josh
  • 35
  • 4
  • What version of Excel are you using? Excel 2007 introduced the [IFERROR() function](https://support.office.com/en-US/Article/IFERROR-function-f59bacdc-78bd-4924-91df-a869d0b08cd5?ui=en-US&rs=en-US&ad=US) which greatly reduces the size of error controlled `VLOOKUP()` formulas. –  Oct 08 '14 at 22:24
  • Jeeped - Developing on Excel 2010, but our company still has some systems rocking out Excel 2003. :( – Josh Oct 08 '14 at 22:32

1 Answers1

1

I suspect what you want may be: 

=IF(D2="","",IF(AND(ISERROR(VLOOKUP(D2,Roster!H:J,3,0)),ISERROR(VLOOKUP(D2,Roster!I:J,2,0))),"Add # to Roster", IF(ISERROR(VLOOKUP(D2,Roster!H:J,3,0)),(VLOOKUP(D2,Roster!I:J,2,0)),(VLOOKUP(D2,Roster!H:J,3,0)))))
pnuts
  • 58,317
  • 11
  • 87
  • 139