#### dirk1965

##### Board Regular
Can the VLookup function be used on worksheets that have header rows? If so, what does the syntax look like?

Thanks

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.

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.

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?

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?

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.

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

Thanks!

Another question:

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

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).

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.

Replies
3
Views
168
Replies
18
Views
761
Replies
1
Views
60
Replies
0
Views
154
Replies
17
Views
484

1,217,503
Messages
6,137,020
Members
450,038
Latest member

### 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.

### Which adblocker are you using?

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

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