Solved

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

Posted on 2016-08-17
9
72 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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 11

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 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 41759474
Sorry, I didn't know you could use VLOOKUP in VBA.
0
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

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 31

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now