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")))))
 
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")
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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"
 
Upvote 0
: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 :oops:
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top