# Advanced VLOOKUP based on the value of two separate drop down lists

I have a spreadsheet with a summary page containing a selection list for week and a selection list for the portfolio. The workbook has the portfolio data on separate tabs. What I require is data for specific weeks from different portfolio tabs to be brought into a cell in the summary sheet.

E.G. Total Budget for week 32 on Portfolio 3

Currently I can do a VLOOKUP to bring the data back based on the week being selected in the drop down, but I need to have the logic to also look at the correct data tab for the right portfolio data.

What I've tried below (that is not currently working) is:

``````=IF(C4="Portfolio 3",VLOOKUP(C3,'Portfolio 3'!D:E,2,IF(C4="Portfolio 4",VLOOKUP(C3,'Portfolio 4'!D:E,2,IF(C4="Portfolio 2",VLOOKUP(C3,'Portfolio 4'!D:E,2,FALSE))))))
``````
• Please share some sample data will help us to fix the issue..also what `C3` contains, `Week number` or what? And one more are you looking for `Total (Sum) of Week 32` for `Portfolio 3` in all Sheets? Jul 10, 2019 at 11:33
• Cont,,, if so then you have two Criteria,, 1. is Week Nu & 2nd is Portfolio 3,, write!! Jul 10, 2019 at 11:43
• C3 contains the week number which is anything between 1-54. The second drop down contains 4 portfolios. E.G. Portfolio 1. I just want to return lookups of the values in the corresponding cell/s on the portfolio tabs, not looking for a total sum, just the current value of a single cell, there is only ever one row per week on the portfolio tabs. Jul 10, 2019 at 12:03
• For example: Financial Week 40 Budget £40,000 Jul 10, 2019 at 12:07
• That should be 52 weeks not 54, couldn't seem to edit the comment Jul 10, 2019 at 12:34

This solution is based on Concatenation method, I've used in both sample Sheets `2` and `3`.

How it works:

• Enter Sheet's Name in Cell `A41 & A42` of `Sheet 1`.

• Write this Formula in `Column B` of both sample Sheets to Concatenate Values `=C2&D2` & fill down.

• Enter combined value in Cell `C41` of Sheet 1.

• Now enter this Array Formula in Cell `D41` of `Sheet 1`, finish with Ctrl+Shift+Enter.

`{=VLOOKUP(C41,INDIRECT("'"&INDEX(\$A\$41:\$A42,MATCH(TRUE,COUNTIF(INDIRECT("'"&\$A\$41:\$A42&"'!B2:E6"),C\$41)>0,0))&"'!B2:E6"),4,0)}`

N.B. As soon you change combination in Cell `C41` you get new data.

# Edited:

You may use this Formula also to pull data across multiple Sheets using more than one criteria.

``````=LOOKUP(9.999999999E+307,CHOOSE({1,2,3},0,LOOKUP(2,1/((Sheet2!\$C\$2:\$C\$6=B\$10)*(Sheet2!\$D\$2:\$D\$6=B\$11)),Sheet2!\$E\$2:\$E\$6),LOOKUP(2,1/((Sheet3!\$C\$2:\$C\$6=B\$10)*(Sheet3!\$D\$2:\$D\$6=B\$11)),Sheet3!\$E\$2:\$E\$62)))
``````

Note:

• Cell `B10` and `B11`are criteria in `Sheet 1`.
• `LOOKUP(9.999999999E+307` is used to find the Largest value that is less than or equal to the lookup value.
• Where `Lookup(2,1/dataRange,,)` calculates an array of `#DIV/0!'s` and `1's`, and `LOOKUP` ignores errors and returns the value associated with the lats numeric element of an array.

Adjust Cell references in the Formula as needed.

• Thank you, unfortunately it doesn't work for me. I can't concatenate the values. It's a very large set of data which makes things harder. The main sheet has drop down selection boxes which can't be concatenated, it's also not a small data range to select from like your example, we are talking thousands of records which will dynamically grow/change. I was hoping there was a way to do this without VBA but I can't see how to write an IF logic statement to just switch between tabs in the formula Jul 10, 2019 at 14:13
• @Clare,, now check the edited part I've included new Formula is rather easier and while using this no need to work with Concatenation !! Jul 11, 2019 at 6:01
• Thanks for your help, that also could work but I ended up trying again with the IF formula and came up with a working solution below. Thank you for your suggestions! Jul 11, 2019 at 8:18
• @Clare,, what I've suggest you has been tested many times before I've posted so there is no question of not working!! May be you trying different than what has been posted as Question by you!! Jul 11, 2019 at 8:23

I found a more suitable solution to the problem that has now worked:

``````=IF(C4="Portfolio 3",SUMIF(Table5[Financial Week],C3,Table5[Portfolio Stock Value]),IF(C4="Portfolio 4",SUMIF(Table6[Financial Week],C3,Table6[Portfolio Stock Value]),IF(C4="Portfolio 2",SUMIF(Table3[Financial Week],C3,Table3[Portfolio Stock Value]),IF(C4="National",SUMIF(Table1[Financial Week],C3,Table1[National Stock Value]),IF(C4="NDC",SUMIF(Table2[Financial Week],C3,Table2[NDC Stock Value]))))))
``````
• Could you edit the post & include the Screen Shot !! Jul 11, 2019 at 8:24
• I can't screenshot as I've had to remove any sensitive names and information. I'm not able to post a screenshot of anything due to the nature of the work. Jul 11, 2019 at 8:32
• , then put some sample data not similar to original to justify your formula,, seems it may not pull correct data if get duplicate!! Jul 11, 2019 at 8:47
• I'm afraid I don't understand your request. I have the formula tested and working.E.G. If I select week 30 for portfolio 3 and there are four values for that week spanning years 2016-2019 it sums those values and returns the total on my summary tab. Jul 11, 2019 at 9:58
• ,, exactly and what you have posted as Question,, Looking for Value for particular Week!! And now you are getting the SUM?? Jul 12, 2019 at 7:21