Need Vlookup experts on this string

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,965
Office Version
2019
Platform
Windows
It returns N/A if all vlookups return an error

If you evaluate, step through to the red section for a welder / green for supervisor, and check that the relevant section results in T,S, or F for welder, 3,2, or 1 for supervisor.

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

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
I have evaluated it, and looking at it I dont see why it dont return the values, but it just is not finding the lookup value in the tables, and is returning N/A.

I dont know what else to try
 

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
I really dont know, I have done that, and it runs all the way through except when finding people that belong to 3rd shift, and then for some reason it returns "S" which is a value that it finds, and is supposed to substitute "2nd" if true, but since false it shouldn't be there.

This code returns welders on 3rd shift as "S", and everyone else as "N/A"
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,965
Office Version
2019
Platform
Windows
:oops: The perils of an untested formula, I could have sworn that the arrays should work that way, this should fix things.

=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","3rd";"S","2nd";"F","1st"},2,0),VLOOKUP(VLOOKUP(O14077,Supervisors.xlsx!Table1[#All],2,0),{3,"3rd";2,"2nd";1,"1st"},2,0))),"N/A")

edit:- just realised, the arrays I used in earlier posts were Hlookup format, not Vlookup :banghead:
 
Last edited:

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
Intriguing, Now it is bringing the correct shift from the welder database, but still returning the "N/A" for the Supervisors as if it cannot find them.
 

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
That was a problem with my table not having the lookup in the first colum of the table., I adjusted and it seems to work ofr now. I will now test with a larger set of data.
 

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
Just tested over a 1000 rows, and life is good, Thank you very much. You seriousely just cut about an hour of work a week out of my spreadsheet.

One more question about this:

If I run the data in 2007, and then save it in compatable mode, will other users be able to: View, Filter, and sort this information on older versions?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,965
Office Version
2019
Platform
Windows
If you copy / pastespecial values then yes, but the formula will return a #NAME? error in versions older than 2007.

If needed I should be able to come up with a workaround formula, although it would be less efficient.

Is use of VBA permissable?
 

finchd4

New Member
Joined
Dec 13, 2011
Messages
44
Problem is solved by your work, and boss is good with its compatibility.

Thank You
 

Forum statistics

Threads
1,089,212
Messages
5,406,879
Members
403,110
Latest member
nataliemur

This Week's Hot Topics

Top