Choose formula combined with Vlookup help

[rtb2399]

Good morning,

I am using Excel 2007 and I am trying to get my formula to lookup for data in multiple spreadsheets if it cannot find the data in one by moving on to another spreadsheet and seeking the data.

Currently here is my formula:

=CHOOSE(C2,VLOOKUP($C2,'[US Multi Factor Model.xls]Revision Model'!$A$6:$DT$888,122,0),VLOOKUP($C2,'[EMG Active.xlsm]Model'!$B$6:$CV$862,97,0),VLOOKUP($C2,'[TOIM INTL Ranks V3 Active.xlsm]DEV UNI'!$B$6:$FT$1120,173,0))

C2= the stock symbol column
and the vlookup's are referencing a ranks in 3 different spreadsheets. If the stock is not in the first vlookup formula I want it to go on and look at the second vlookup and then the third if not the case. Also if the formula can not find and data on the referenced symbol i would like it to return a "NR"
- Could someone please tell me what I am doing wrong here. Thank you so much in advance and please feel free to ask me any other questions

 

[JoeMo]

The first argument in CHOOSE has to be an integer value (1,2,...) that determines which of the other arguments will be evaluated. Are columns A, B, and C where your stock symbols are placed? If they are, your formula will evaluate one of the three Vlookups corresponding to the value in C2. It will not evaluate the other two Vlookups.

 

[rtb2399]

How do i get it to cycle through the other vlookups and determine which spreadsheet has the answer without providing a numerical value of which spreadsheet to look into? Column C is where all the symbols are located..

 

[JoeMo]

Add the three lookups together like this:

=IFERROR(VLOOKUP($C2,'[US Multi Factor Model.xls]Revision Model'!$A$6:$DT$888,122,0),0) + other two vlookups, each in iferror wrapper