Thread: VLookup, or Index and NestedIF's

1. VLookup, or Index and NestedIF's

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

2. Welcome to the Lounge.

A sample workbook would help.

3. ..and what Excel version are you using???
(The new Excel2016 has a new =IFS(...) function

zeddy

4. Sample workbooks

Originally Posted by zeddy
..and what Excel version are you using???
(The new Excel2016 has a new =IFS(...) function

zeddy
Hi Zeddy,

I have Excel 2010. I have attached a couple of sample workbooks that show what I am trying to do.

Thank you,

Dan

5. I cannot tell from this what you're trying to get in the "Filter workbook" in B5.

Unless you plan to fill the formula, ROW(\$A\$1) is a 1, so why not use that single digit?

At least for me, I need more specific info.

6. The Following User Says Thank You to kweaver For This Useful Post:

SavGDK (2016-04-05)

7. Hi Dan

I agree with kweaver that it is a little confusing regarding what you are trying to get.

Perhaps you could get what you want using Autofilters and macros. See attached workbook (you need to open it with macros enabled)

So this is how I did it..
I copied the block of data from columns [C] to [AE] from your posted file [T4 Test.xlsx] and pasted this block as values to the same location on [Sheet1] in the attached workbook. (This could be done via an 'import macro').

On [Sheet3] I added a named range [block]

On [Sheet1], I added a lookup formula in [AF4] and copied this formula down.
The top 3 cells in column [AF] use the same formula to reference the drop-down selection of cell [B1] (this is required to make the autofilters work OK).

I added a vba routine to execute an autofilter based on the drop-down value as selected in cell [B1]. This routine also hides all filter dropdown arrows in all columns (except you must have at least one, so I left column [AF] )

I then added a change-event-macro for the code in [Sheet1] which runs the autofilter macro whenever the cell value in cell [B1] changes i.e. whenever you change the drop-down value in cell [B1]
The matching records are then displayed.

zeddy

8. The Following User Says Thank You to zeddy For This Useful Post:

SavGDK (2016-04-05)

9. Kweaver and Zeddy,

Thank you for the replies. I apologize for the poor description of what I was trying to accomplish. My original plan was that depending on what mode was chosen in the filter spreadsheet for example Hyr, it would then do a lookup in the T4 spreadsheet and import just the data that matches that criteria, same for any other chosen modes. My first attempt, bear in mind I am a newbie at this, did not work out well and I could not figure out how to get it to go to the other sheet and import just the chosen data.

Zeddy your proposed solution is great I as trying to limit the amount of data imported each time to keep the spreadsheet size at a minimum. I appreciate the help very much.

Thank you,

Dan

10. Hi Dan

..one way you can keep spreadsheet size to a minimum is to save Excel files in .xlsb format.

zeddy

11. The Following User Says Thank You to zeddy For This Useful Post:

SavGDK (2016-04-05)

12. I will try that thank you.

Dan

13. Originally Posted by zeddy
Hi Dan

I agree with kweaver that it is a little confusing regarding what you are trying to get.

Perhaps you could get what you want using Autofilters and macros. See attached workbook (you need to open it with macros enabled)

So this is how I did it..
I copied the block of data from columns [C] to [AE] from your posted file [T4 Test.xlsx] and pasted this block as values to the same location on [Sheet1] in the attached workbook. (This could be done via an 'import macro').

zeddy

Zeddy,

I am just wondering the easiest way to create the import macro? Is there a function in excel that will do that or would it have to be done with VBA?

thanks,
Dan

14. Hi Dan

There are several ways you could import data into the 'Filter workbook'.
For example, you could use a Data Query method.
It all depends on how consistent the source file is.
For example, if data workbook has a the same name etc etc etc. and how automated you want the process to be.
It might be simpler to use VBA, and allow the User to click a macro button to display a 'File Selection' browser, to point to and choose the source data workbook etc etc etc.

zeddy

15. The Following User Says Thank You to zeddy For This Useful Post:

SavGDK (2016-04-07)

16. Originally Posted by zeddy
Hi Dan

There are several ways you could import data into the 'Filter workbook'.
For example, you could use a Data Query method.
It all depends on how consistent the source file is.
For example, if data workbook has a the same name etc etc etc. and how automated you want the process to be.
It might be simpler to use VBA, and allow the User to click a macro button to display a 'File Selection' browser, to point to and choose the source data workbook etc etc etc.

zeddy
Thanks for the info.

Dan