#1




VLookup and Nested IFs or Index Function
I have 2 spreadsheets one that is retrieving data from a database (Vlookupdata) and the second spreadsheet (Filter workbook) I want to use to retrieve data from the first workbook. I want the second workbook to retrieve data based on the value in the List in cell B1. The data in the first workbook column AE is what determines what data is retrieved. Based on the values in the List "BPass" for example is for all the data greater than or equal to 600, NCY is for all data between the values of 528  60, HYR is for the data with the values between 400  527, A pass is for data less than or equal to 399, and All is for all data. I am struggling to figure out how to do the Vlookup and the If statements with the ranges. If anybody can give me some help it would be greatly appreciated.
It occurs to me that the Index Function may also be a way to do this but I can't quite figure that out. Thank you, Dan 
#2




First off, why use two workbooks? Why not use another sheet in the same workbook.
Second, there is no data in AE in the SavRef workbook that I can see. I have no idea what BPass is. 
#3




Hi, The workbook that is pulling data from the server will be quite large so I was trying to limit the size by pulling the data into a separate workbook and manipulate only the data I need at the time in that one. I thought I had left column AE in there with data I may have had to cut it out in order to get the file small enough to upload it. Either way the data I need to filter off (BPass for example) will be in the column furthest to the right.
My other thought was the less I had other people accessing the workbook pulling data from the server the less chance there is of corrupting that workbook. The effort involved setting that one up is quite involved. Thanks for asking, Dan 
#4




To me it is quite unclear what you want.
You want to retrieve data from the first workbook (SaveRefExample.xlsx I guess) based on the value in the List in cell B1. Which List. Do you mean Sheet1!B1 in Filter workbook.xlsx? You mention something about BPass, HYR etc. but I do not find any of these in SaveRefExample.xlsx. If you still want help I think you should make an effort to explain (in the file SaveRefExample) exactly what it is you want and give more examples of required results. 
#5




Clarification
Good Morning XOR,
Hopefully I can explain this a little better, I apologize for the confusion. From the beginning.. The data I am collecting is collected in The SAVRefExample Worksheet  Sheet 4. The filter workbook Sheet 1 is where I am trying to pull the data into based on the mode I am looking for. the mode is selected in cell B1, that list and all the parameters is fed from Sheet 3 of the Filter workbook. Next to each mode on Sheet 3 of the Filter Workbook is the temp I want to filter by, those temperatures are reflected in the SavRefExample workbook, sheet 4 in cell AE. Hopefully that clears up the confusion. I appreciate the help. Dan Last edited by SavGDK; 04082016 at 05:27 AM. Reason: greeting wrong 
#6




OK  with HYR in Filter workbook Sheet1!B1 what exactly should be returned in same workbook Sheet3, and where in that sheet, cf. my request for examples of required results in my latest post.
How can I know that HYR in Filter workbook Sheet1!B1 means that shall find the data in SaveRefExample, column AE? And by the way  I do not see any data in that column AE which is between 400 and 527.99 Sorry, but I am still at a loss. 
#7




Sorry for the confusion. The list that is in Filter Workbook Sheet 1 that nameset is listed on sheet 3 of the same workbook, the column next to the nameset has all the parameters. What I want returned is the data from SavRefExample workbook that meets the parameters i.e. that is between those temperatures. So I want all the columns returned, as long as the value in column AE in the SavRefExample meets the criteria, based on what is selected in the filter workbook.
As far as how you can know, I was trying to figure out how to right the IF statements to look at the numbers in Column AE in the SavRefExample workbook to return the appropriate data, maybe there is no way to do it using Vlookup and If statements. The way I pictured it working was the mode selected in the Filter Workbook Sheet 1 CellB1, would then go out and query all the data that matches the ranges determined by the mode, and then pull that data into the Filter workbook for further analysis. That way there is little chance we will corrupt the other workbook. I updated the data and reattached here Dan Last edited by SavGDK; 04082016 at 05:59 AM. Reason: forgot attachement 
#8




Take a look at the attached files.

#9




XOR,
This is very close to what I wanted and the idea of getting it there by the references I think will work. One question how do I get the Filter workbook to update when I change the value in the SavRef workbook? Thank you very much. Dan 
#10




If I change for example cell D14 (in SaveRef) from 133,2 to say 999999 that value immediately appears in cell AM12 and in the Filter workbook Sheet1!D7.
Doesn't the same happen to you? Now it is bedtime for me so don't expect any response the first ten hours or so. 
#11




I forgot a couple of things.
You should clear the data validation in SavRef, AL7 as this cell contains a simple reference to [Filter.xlsx]Sheet1'!$B$1. I have inserted helper cells in SavRef, AF12:AF27. You can move these to another place if you want, but don't delete. The formulas in SavRef AL12:BN21 are array formulas. Please be aware that array formulas require a special way of entering. Normally you just press Enter when finishing a formula. Array formulas must be entered by holding down Ctrl and Shift before pressing Enter. If you do it correct Excel will automatically put braces {} around the formula. Do not try to put these braces manually. When you have entered one array formula correctly you can copy to other cells as you normally do. If you extend the data range then remember to redefine d in the Name Manager. Be aware that array formulas are calculation intensive, so if you have really many rows (and columns) of data you may experience a reduced recalculation time. 
Tags 
if statement, index, vlookup 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
using if and nested vlookup  wentworth16238  Excel  5  06142015 12:59 PM 
Nested Countif Function  bdavidson22  Excel  1  12212014 07:55 AM 
Nested Countif Function  bdavidson22  Excel  0  12192014 12:36 PM 
Nested Vlookup Help  asluder2000  Excel  2  11142014 11:00 PM 
Nested vlookup with varable tables!  Dave Jones  Excel  0  08302012 09:15 AM 