Are Multiple VLookups possible?

[D4WNO77]

Hi everyone,

I've come across a bit of a challenge at work that I've been trying to find an answer on for days - I would REALLY appreciate some help on this so thanks in advance if anyone can help; I'll try and explain the best I can:

The main sheet contains what we can CCRT references which range from CC_RT00000 to CC_RT03999

I open all 6 of the smaller sheets, these contain "CC_RT_CCL" numbers

My aim is to link the relevant CC_RT_CCL references in the 6 smaller sheets into an empty field next to the CC_RT numbers in the main sheet, next to it's corresponding one. I do this currently as below:

Current process: I find all lines in MAIN sheet with a "CC_RT" number between 00000-00999 and enter in one VLOOKUP on the empty field and drag down the relevant lines. Then for fields from 01000-01999 I enter in a slightly different VLOOKUP and drag that down and so on and so forth. Ideally, I'd love to have a single VLOOKUP that I can enter in the top field in the MAIN spreadsheet which I can copy all the way down the spreadsheet and that it treats the lines (depending on their number range) in the way in which is relevant.

Phew, I hope this makes sense, it's quite tricky to explain. FYI I've put copies of the 6 VLOOKUPS I'm currently using as below (The final one is for some lines we have with a slightly different format)


1 =VLOOKUP(cell,'[CC_CCL_0001_01 (21000-23000).xls]CCL'!$A$2:$AG$2002,23,FALSE)
2 =VLOOKUP(cell,'[CC_RT_CCL_0001_01 (CC_RTs 00000-00999).xlsx]CCL'!$A$2:$Z$1001,25,FALSE)
3 =VLOOKUP(L2,'[CC_RT_CCL_0001_01 (CC_RTs 01000-01999).xlsx]CCL'!$A$2:$Z$1001,25,FALSE)
4 =VLOOKUP(L4,'[CC_RT_CCL_0001_01 (CC_RTs 02000-02999).xlsx]CCL'!$A$2:$Z$1001,26,FALSE)
5 =VLOOKUP(L19,'[CC_RT_CCL_0001_01 (CC_RTs 03000-03999).xlsx]CCL'!$A$2:$Z$1001,26,FALSE)
6 =VLOOKUP(L94,'[RT_CCL_0001_01 (RTs 00000-00999).xlsx]CCL'!$A$2:$Z$1001,26,FALSE)

[adaws]

Can you attach a file with some data so that we can work on it?