Need Vlookup experts on this string

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
Hi,

I am looking for some suggestions on why this string isnt working.

Background: This code used to be three nested if statements that sepperated people out by thier shift when searching for them using a id (this searched for shift was in a different file, "welder Database")

Then I manipulated it with the IF OR statement on the front for a new criteria of filtering basically.

Now I am looking at adding three more If statments to search a different file "Supervisors" for thier shifts.

We want to keep the supervisors sepperate from our welder database, otherwise I would just add them to the other one.

I have the first of the new IF statements on here searching for wheather or not they are on second shift. I have second first because that is where the first instance that comes up is and I'm just testing a few rows at this point. Problem is, I know the Supervisor is on Second and it is still returning a "#N/A" Error.

Any suggestions will be appreciated.

Thank you,

=IF(OR(MASTER[[#This Row],[Length Rejected (cm)]]="EMPTY",MASTER[[#This Row],[Welder]]="Supplier"),"",IF(VLOOKUP(O14077,'S:\Fab Planning\Weld School\Welder Database\[4.16.09 Peoplesoft Welder and Robot Database list v3.xls] Welders'!$C$1:$H$65536,6,FALSE)="T","3rd",IF(VLOOKUP(O14077,'S:\Fab Planning\Weld School\Welder Database\[4.16.09 Peoplesoft Welder and Robot Database list v3.xls] Welders'!$C$1:$H$65536,6,FALSE)="S","2nd",IF(VLOOKUP(O14077,'S:\Fab Planning\Weld School\Welder Database\[4.16.09 Peoplesoft Welder and Robot Database list v3.xls] Welders'!$C$1:$H$65536,6,FALSE)="F","1st",IF(VLOOKUP(O14077,Supervisors.xlsx!Table1[#All],2,FALSE)="2","2nd","N/A")))))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,149
Office Version
2019
Platform
Windows
Which version of excel are you using?

With excel 2007 or newer you could cut that formula down significantly, making it easier to see where it's going wrong.
 

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
Which version of excel are you using?

With excel 2007 or newer you could cut that formula down significantly, making it easier to see where it's going wrong.
I am using 2007, But alot of the department is still using older versions. Would this idea of yours be able to be compatible, and if not.... I may be interested anywise.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,149
Office Version
2019
Platform
Windows
This includes 1st, 2nd and 3rd for welders and supervisors, unfortunately it isn't compatible with older versions of excel, only 2007 or newer.

=IFERROR(IF(OR(MASTER[[#This Row],[Length Rejected (cm)]]="EMPTY",MASTER[[#This Row],[Welder]]="Supplier"),"",IFERROR(VLOOKUP(VLOOKUP(O14077,'S:\Fab Planning\Weld School\Welder Database\[4.16.09 Peoplesoft Welder and Robot Database list v3.xls] Welders'!$C$1:$H$65536,6,FALSE),{"T","S","F";"3rd","2nd","1st"}2,0),VLOOKUP(VLOOKUP(O14077,Supervisors.xlsx!Table1[#All],2,FALSE),{3,2,1;"3rd","2nd","1st"},2,0))),"N/A")
 

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
This includes 1st, 2nd and 3rd for welders and supervisors, unfortunately it isn't compatible with older versions of excel, only 2007 or newer.

=IFERROR(IF(OR(MASTER[[#This Row],[Length Rejected (cm)]]="EMPTY",MASTER[[#This Row],[Welder]]="Supplier"),"",IFERROR(VLOOKUP(VLOOKUP(O14077,'S:\Fab Planning\Weld School\Welder Database\[4.16.09 Peoplesoft Welder and Robot Database list v3.xls] Welders'!$C$1:$H$65536,6,FALSE),{"T","S","F";"3rd","2nd","1st"}2,0),VLOOKUP(VLOOKUP(O14077,Supervisors.xlsx!Table1[#All],2,FALSE),{3,2,1;"3rd","2nd","1st"},2,0))),"N/A")
Gives Me an Error Here: See Highlighted Above. What does the 2,0 syntax mean?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,149
Office Version
2019
Platform
Windows
Oops, I missed a comma

=IFERROR(IF(OR(MASTER[[#This Row],[Length Rejected (cm)]]="EMPTY",MASTER[[#This Row],[Welder]]="Supplier"),"",IFERROR(VLOOKUP(VLOOKUP(O14077,'S:\Fab Planning\Weld School\Welder Database\[4.16.09 Peoplesoft Welder and Robot Database list v3.xls] Welders'!$C$1:$H$65536,6,0),{"T","S","F";"3rd","2nd","1st"},2,0),VLOOKUP(VLOOKUP(O14077,Supervisors.xlsx!Table1[#All],2,0),{3,2,1;"3rd","2nd","1st"},2,0))),"N/A")

column 2 of the vlookup, 0 = false.

Vlookup uses the array constant in place of a range, {"T","S","F";"3rd","2nd","1st"} is the same as a 3 row, 2 column range, Commas delimit the rows, semicolons delimit the columns.
 

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
Would the table that the first set of vlookups uses have to be in 2007 format? because I didnt get an error this time, but now it returns N/A as the code states it should for all the welders, and supervisors?

I see that it now has my entire file path to the second file though, and that one is in 2007.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,149
Office Version
2019
Platform
Windows
That shouldn't make any difference, have you tried stepping through with formula evaluation to see where the error is starting?

Basic principle, red section handles welders, green handles supervisors,

=IFERROR(IF(OR(MASTER[[#This Row],[Length Rejected (cm)]]="EMPTY",MASTER[[#This Row],[Welder]]="Supplier"),"",IFERROR(VLOOKUP(VLOOKUP(O14077,'S:\Fab Planning\Weld School\Welder Database\[4.16.09 Peoplesoft Welder and Robot Database list v3.xls] Welders'!$C$1:$H$65536,6,0),{"T","S","F";"3rd","2nd","1st"},2,0),
VLOOKUP(VLOOKUP(O14077,Supervisors.xlsx!Table1[#All],2,0),{3,2,1;"3rd","2nd","1st"},2,0))),"N/A")
 
Last edited:

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
Basically What I am getting currently is that the "OR" statement is working, but none of the lookups are finding anytihng with this one.
 

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
It is not giving an error now, it is returning the "N/A" value that it is suppos3ed to do upon the event of not finding anything in the vlookups.
 

Forum statistics

Threads
1,081,899
Messages
5,361,928
Members
400,666
Latest member
UDLearning

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top