Solved

VBA - Excel, is there a easy way to vlookup a certain range?

Posted on 2016-08-17
9
99 Views
Last Modified: 2016-08-23
I need to make do a vlookup in 12 columns with about 92 rows, is there a way to to do vlookup without to create formulas for each cell?
0
Comment
Question by:Hakum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 12

Expert Comment

by:Missus Miss_Sellaneus
ID: 41759464
You only have to create one for the top row of each column. Use "$" before the starting & ending row reference for your lookup range and you can just copy down the formulas.
0
 
LVL 1

Author Comment

by:Hakum
ID: 41759468
yeah i'm fully aware of this but what if i needed to make this in VBA?
0
 
LVL 12

Expert Comment

by:Missus Miss_Sellaneus
ID: 41759474
Sorry, I didn't know you could use VLOOKUP in VBA.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Hakum
ID: 41759504
I just created a quick sample file.. sample.xlsx

please have a look,

But in short let me explain, every month a list is exported from BI and needs to be put into lookedup and data is filled into sheet.

There is around 100 rows every month that needs to lookedup and for that i would like to use vba so i can reuse this every year. I dont feel like mapping up 100 rows times 12 columns, so i was thinking if there could be a easier way and much quicker was to do this that why VBA since i have used vlookup in VBA before but just for one single cell  but not 100 cells.

hope this put some light on the case.
1
 
LVL 19

Expert Comment

by:Serena Hsi
ID: 41759670
I'd use a Pivot Table instead of a vlookup formula, the tool marketers love to use to create fancy reports in Excel.

Here is a code sample for dynamically changing a pivot table's data source via VBA, and a walk-through on how to use it.
0
 
LVL 2

Accepted Solution

by:
psteff earned 250 total points
ID: 41761015
I might be missing something here, but if you are always wanting the "lookup" table to be showing the full calendar year (always January to December) for any given year, then the simpler method of populating your "lookup" table with formulas can be done once.  The only times there would need to be any changes is if you need to add lookup rows to it for additional products (or whatever you are looking up).  Then it would be a simple copy of the formulas for the additional rows.

I have attached a sample.  The first table does it by the vlookup.  The second one does it using the sumif function.

Note - I was getting inconsistent results - then I realized that your data and your lookup were not formatted the same.  The numbers to be looked up need to be in the same format in both places (either both text or both in the number format) in order for the vlookup to work correctly.
sample.xlsx
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 41765103
As the Data source is Dynamic you can create a Dynamic named range using OFFSET function.

On the Formula tab select Name manager, click New and add a Name (I have used DATA) and in the Refers to box copy and paste the formula below:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A)+1,COUNTA(Data!$1:$1)+1)

This sets a Range that starts at A1 of Data sheet and goes down as many rows are there are entries in column A and across as many columns as there are entries in Row 1.

As mentioned above, some of the entries in column A of Data are numeric and some are text. The following formula allows for both and for non-existent data in future months:

=IFERROR(IF(ISERROR(VLOOKUP($B4,DATA,C$2+1,FALSE)),VLOOKUP(TEXT($B4,"0"),DATA,C$2+1,FALSE),VLOOKUP($B4,DATA,C$2+1,FALSE)),0)

This refers to the previously created Dynamic Named Range "DATA" and uses the month value in row C of the vlookup sheet to determine which column to return from the data area.

The TEXT function converts the lookup value to text if it can't find the numeric equivalent.

The IFERROR function gets rid of #REF errors for columns where data is not present yet.

Once you have one row of formulae, you can fill down the formulae relatively simply as the list in column A or B expands. Highlight the cells in columns C to N of the last row and then double click the bottom right corner of the cell in column N. This will fill down as far as required for entries in column B.

Thanks
Rob
0
 
LVL 1

Author Closing Comment

by:Hakum
ID: 41767087
Thanks guys!
0
 
LVL 2

Expert Comment

by:psteff
ID: 41767349
You are welcome.  Glad to be of help!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question