Search to Match From Two Different Tabs?

[Zero3]

We have the following formula in our timesheets. Basically when we enter a Job number in one cell in say Tab1, it will return the matching project name in a different cell that we have next to the project number. The job number match is done in the tab named "ProjectSchedule", where all the details of the project are listed. We need to extend the match range to include another tab named "CustomSchedule", but I can't figure out how to make the first formula search jobs from both tabs.

Current working formula:

=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202,0)))

What I'm trying to accomplish, but it not working is something like this:

=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202&CustomSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202&CustomSchedule!$B$3:$B$202,0)))

Please help.

Thanks.

[robert111]

=concatenate(vlookup(number,table1,2),vlookup(number,table2,2))

is the basic structure, if you need spaces or special characters, build it into the concatenate statement

eg =concatenate("dog"," ","cat") returns dog cat (3 spaces between)

[NBVC]

We have the following formula in our timesheets. Basically when we enter a Job number in one cell in say Tab1, it will return the matching project name in a different cell that we have next to the project number. The job number match is done in the tab named "ProjectSchedule", where all the details of the project are listed. We need to extend the match range to include another tab named "CustomSchedule", but I can't figure out how to make the first formula search jobs from both tabs.

Current working formula:

=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202,0)))

What I'm trying to accomplish, but it not working is something like this:

=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202&CustomSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202&CustomSchedule!$B$3:$B$202,0)))

Please help.

Thanks.

Try:

Please Login or Register  to view this content.

[Zero3]

NBVC,

That seems to be the correct code for what I need to accomplish but for some reason it isn't working for me. My projectschedule tab is still being matched but the customschedule is still inputing a n/a. I am not sure what the problem is considering there is no difference between the two tabs. Although the projectschedule tab is actually a referenced tab from a external master sheet.

[Zero3]

Thank you very much for your help. That formula is correct I just had my text set to general in my custom tab and it needed to be set to text in order for the formula to read the number. Thank you.