Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 127
  • Last Modified:

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

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
Hakum
Asked:
Hakum
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
Missus Miss_SellaneusCommented:
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
 
HakumAuthor Commented:
yeah i'm fully aware of this but what if i needed to make this in VBA?
0
 
Missus Miss_SellaneusCommented:
Sorry, I didn't know you could use VLOOKUP in VBA.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HakumAuthor Commented:
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
 
Serena HsiMarketing ConsultantCommented:
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
 
psteffCommented:
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
 
Rob HensonFinance AnalystCommented:
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
 
HakumAuthor Commented:
Thanks guys!
0
 
psteffCommented:
You are welcome.  Glad to be of help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now