Lookup References

[SirDraco]

I am trying to do a look up to grab the correct information with multiple look up references and for it to show blank instead of #N/A or #Di/V!

The Tab called post calls in the drivers cell i need the formula to look up the depot and then go to the crew phone numbers and look up the home depot, making sure that they match the depot list on the data tab.

Then still in the same cell to then look at the route number against the route number on the drivers tab.

Then still in same cell with the lookup to find the driver name and then make sure the name matches a name in the all name table (Crew name)

This will then display the crew name, and if the name dose not match it will be a blank cell.

Thanks for any help in advance.

[SirDraco]

I have tried to use code...

=VLOOKUP(INDEX('Crew Numbers'!A5:L105,MATCH('Post Calls'!G5&'Post Calls'!E5,'Crew Numbers'!A&'Crew Numbers'!E,0)),'All Names'!D6:D532,1,FALSE)

But this comes back with #NAME?

So tried the bellow to see if would then leave as a blank cell

=IF(VLOOKUP(INDEX('Crew Numbers'!A5:L105,MATCH('Post Calls'!G5&'Post Calls'!E5,'Crew Numbers'!A&'Crew Numbers'!E,0)),'All Names'!D6:D532,1,FALSE)="","",VLOOKUP(INDEX('Crew Numbers'!A5:L105,MATCH('Post Calls'!G5&'Post Calls'!E5,'Crew Numbers'!A&'Crew Numbers'!E,0)),'All Names'!D6:D532,1,FALSE))

i did also try

=VLOOKUP(VLOOKUP($G6,'Crew Numbers'!$A$5:$L$65536,10,FALSE),'All Names'!$D$6:$D$65536,1,FALSE)

This dose the look up of the route to find the name and then looks up the name against all names but if there is 2 depots using the same route then it shows the wrong information.
So is there a way to expanded on this to get it to also do the depot look up?
Then i would also want to expanded it to be able to be a blank cell if it dose not match up.


Again Thanks for any help in advance.

[SirDraco]

I am still trying to work out the formula for this to work.

If i use ....
=VLOOKUP(VLOOKUP($G6,'Crew Numbers'!$A$5:$L$65536,10,FALSE),'All Names'!$D$6:$D$65536,1,FALSE) This will Display the crew name against the Route number.

If i use ....
=VLOOKUP((VLOOKUP(VLOOKUP(E6,Data!K3:L49,2,False),'Crew Numbers'!$B$5:$L$65536,9,False),'All Names'!$D$6:$D$65536,1,FALSE) This will Display the name against the depot.


I am getting closer but still not able to get it to look up both the route and depot and get it to come back with the correct information.

I have also tried to use code...
=IF(ISERROR(INDEX('Crew Numbers'!A5:L78,MATCH('Post Calls'!G7,'Crew Numbers'!A5:L5,10),MATCH(VLOOKUP(E7,Data!K3:L49,2,FALSE),'Crew Numbers'!B5:L78,9)))=FALSE,INDEX('Crew Numbers'!A5:L78,MATCH('Post Calls'!G7,'Crew Numbers'!A5:L5,10),MATCH(VLOOKUP(E7,Data!K3:L49,2,FALSE),'Crew Numbers'!B5:L78,9)),"")

This is is just loading up a blank cell, so not sure what i need to change for it to get a display.

I have also tried to use code...
=IF(ISERROR(INDEX('Crew Numbers'!A5:L78,MATCH('Post Calls'!G7,'Crew Numbers'!A5:L5,10),MATCH(VLOOKUP(E7,Data!K3:L49,2,FALSE),'Crew Numbers'!B5:L78,9)))=FALSE,INDEX('Crew Numbers'!A5:L78,MATCH('Post Calls'!G7,'Crew Numbers'!A5:L5,10),MATCH(VLOOKUP(E7,Data!K3:L49,2,FALSE),'Crew Numbers'!B5:L78,9)),"VLOOKUP('Crew Numbers'!5:L78,'All Names'!D6:D532,1,FALSE))

This comes up with #N/A so not sure again on how i can get this to show correctly.


Again Thanks for any help in advance.