VLookup w/ Headers

dirk1965

Board Regular
Joined
Feb 18, 2004
Messages
241
Can the VLookup function be used on worksheets that have header rows? If so, what does the syntax look like?

Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Don't quite understand what you mean?

The Vlookup() function should work whether there is a header row or not. It looks for a match in the left most column of your lookup table and returns the result in the corresponding column that you index.
 

dirk1965

Board Regular
Joined
Feb 18, 2004
Messages
241
Well, somehow my formula is picking the 'range lookup' from the column next to it.

Here is my formula:

=VLOOKUP(E2,Sheet1!B:D,2,FALSE)

It actually populating my results with data from column E of Sheet1 instead of D as I'm specifying.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Not sure how it's picking up from column E with that formula....but to pick up from column D, you would need to change the column index number to 3..

=VLOOKUP(E2,Sheet1!B:D,3,FALSE)


Is column B the column you're looking for a match to E2?
 

dirk1965

Board Regular
Joined
Feb 18, 2004
Messages
241

ADVERTISEMENT

something else odd is going on.... Its still pulling in data from somewhere, but can't trace it. I'll look at it more.

Question: What is the column index actually used for?
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
The column index specifies the column number within the lookup table you want to extract data from.

So if your lookup table is Columns B to D, then Column D is Index number 3 (i.e. B=1, C=2, D=3).


Your formula as is should lookup the value/string that is in E2 and find it within Column B of Sheet1. If found, it should return what is in the corresponding cell of column D.

Make sure that if E2 contains a number that it is formatted as a number or general and B2 is formatted likewise.
 

dirk1965

Board Regular
Joined
Feb 18, 2004
Messages
241

ADVERTISEMENT

Ahhh... thats something I never understood. You've explained it very well.

Thanks!
 

dirk1965

Board Regular
Joined
Feb 18, 2004
Messages
241
Another question:

I know that VLookup only returns the first value it hits. If there are multiple values, can they be all be returned?
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
It's not as simple as a vlookup:

There are many ways, but here is one way.

for you data, use =IF(ROWS($A$1:A1)>COUNTIF($B$1:$B$1000,$E$2),"",INDEX($D$1:$D$1000,SMALL(IF($B$1:$B$1000=$E$2,ROW($A$1:$A$1000)-ROW($A$1)+1),ROWS($A$1:A1))))

Notes: you will have define definite ranges....you can't use full column references because this is an array formula.

After you've made changes to the formula, confirm it with Ctrl+Shift+Enter not just ENTER. If done correctly you'll see {} brackets around the formula, then just copy it down the column as far as necessary to get all possible matches. When no more matches are found, blanks are returned.

The formula can be slow if you have a large amount of data to look up so keep column ranges as short as possible.

You may also wish to extract parts of the formula and evaluate them separately (like the Countif) and then refer to that cell in the formula. This reduces repeat calculations.

Alternatively, VBA may be faster (I'm just not savvy enough yet to recommend anything).
 

dirk1965

Board Regular
Joined
Feb 18, 2004
Messages
241
You lost me on this one :)

I don't see how two different sheets are used in your formula such as the VLookup function does.
 

Forum statistics

Threads
1,136,267
Messages
5,674,729
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top