Microsoft Office Forums VLookup and Nested IFs or Index Function

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-04-2016, 05:38 AM
SavGDK SavGDK is offline VLookup and Nested IFs or Index Function Windows 7 64bit VLookup and Nested IFs or Index Function Office 2010 64bit
Novice
VLookup and Nested IFs or Index Function
 
Join Date: Apr 2016
Location: Savannah Ga
Posts: 20
SavGDK is on a distinguished road
Default 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 "B-Pass" 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
Attached Files
File Type: xlsx Filter workbook.xlsx (366.4 KB, 6 views)
File Type: xlsx SavRefExample.xlsx (100.0 KB, 4 views)
Reply With Quote
  #2  
Old 04-05-2016, 12:44 PM
gebobs gebobs is offline VLookup and Nested IFs or Index Function Windows 7 64bit VLookup and Nested IFs or Index Function Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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 B-Pass is.
Reply With Quote
  #3  
Old 04-05-2016, 12:56 PM
SavGDK SavGDK is offline VLookup and Nested IFs or Index Function Windows 7 64bit VLookup and Nested IFs or Index Function Office 2010 64bit
Novice
VLookup and Nested IFs or Index Function
 
Join Date: Apr 2016
Location: Savannah Ga
Posts: 20
SavGDK is on a distinguished road
Default

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 (B-Pass 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
Reply With Quote
  #4  
Old 04-07-2016, 09:45 PM
xor xor is offline VLookup and Nested IFs or Index Function Windows 10 VLookup and Nested IFs or Index Function Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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 B-Pass, 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.
Reply With Quote
  #5  
Old 04-08-2016, 05:26 AM
SavGDK SavGDK is offline VLookup and Nested IFs or Index Function Windows 7 64bit VLookup and Nested IFs or Index Function Office 2010 64bit
Novice
VLookup and Nested IFs or Index Function
 
Join Date: Apr 2016
Location: Savannah Ga
Posts: 20
SavGDK is on a distinguished road
Default 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
Attached Files
File Type: xlsx Filter workbook.xlsx (65.6 KB, 3 views)
File Type: xlsx SavRefExample.xlsx (12.1 KB, 1 views)

Last edited by SavGDK; 04-08-2016 at 05:27 AM. Reason: greeting wrong
Reply With Quote
  #6  
Old 04-08-2016, 05:44 AM
xor xor is offline VLookup and Nested IFs or Index Function Windows 10 VLookup and Nested IFs or Index Function Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #7  
Old 04-08-2016, 05:58 AM
SavGDK SavGDK is offline VLookup and Nested IFs or Index Function Windows 7 64bit VLookup and Nested IFs or Index Function Office 2010 64bit
Novice
VLookup and Nested IFs or Index Function
 
Join Date: Apr 2016
Location: Savannah Ga
Posts: 20
SavGDK is on a distinguished road
Default

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
Attached Files
File Type: xlsx SavRefExample.xlsx (12.2 KB, 1 views)

Last edited by SavGDK; 04-08-2016 at 05:59 AM. Reason: forgot attachement
Reply With Quote
  #8  
Old 04-08-2016, 10:46 AM
xor xor is offline VLookup and Nested IFs or Index Function Windows 10 VLookup and Nested IFs or Index Function Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Take a look at the attached files.
Attached Files
File Type: xlsx Filter.xlsx (75.1 KB, 6 views)
File Type: xlsx SavRef.xlsx (22.5 KB, 3 views)
Reply With Quote
  #9  
Old 04-08-2016, 11:07 AM
SavGDK SavGDK is offline VLookup and Nested IFs or Index Function Windows 7 64bit VLookup and Nested IFs or Index Function Office 2010 64bit
Novice
VLookup and Nested IFs or Index Function
 
Join Date: Apr 2016
Location: Savannah Ga
Posts: 20
SavGDK is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 04-08-2016, 12:01 PM
xor xor is offline VLookup and Nested IFs or Index Function Windows 10 VLookup and Nested IFs or Index Function Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #11  
Old 04-08-2016, 10:06 PM
xor xor is offline VLookup and Nested IFs or Index Function Windows 10 VLookup and Nested IFs or Index Function Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
Reply

Tags
if statement, index, vlookup

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLookup and Nested IFs or Index Function using if and nested vlookup wentworth16238 Excel 5 06-14-2015 12:59 PM
Nested Countif Function bdavidson22 Excel 1 12-21-2014 07:55 AM
Nested Countif Function bdavidson22 Excel 0 12-19-2014 12:36 PM
Nested Vlookup Help asluder2000 Excel 2 11-14-2014 11:00 PM
Nested vlookup with varable tables! Dave Jones Excel 0 08-30-2012 09:15 AM


All times are GMT -7. The time now is 09:32 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft