VLOOKUP on multiple worksheets

The good folks at Extend Office have posted a formula which can be used to run a VLOOKUP formula on multiple Excel worksheets simultaneously.

The formula is an array formula and looks like this:

=VLOOKUP(C1,INDIRECT("'"&INDEX(baseball,MATCH(1,--(COUNTIF(INDIRECT("'"&baseball&"'!$A$1:$T$200"),C1)>0),0))&"'!$A$1:$T$200"),6,FALSE)

. . . array formulas should be entered in a cell while you press CTRL + SHIFT so that brackets that appear at both ends.

In this example we are working with pitching statistics for the 1915 baseball season. The data for the American, Federal and National leagues are on three separate worksheets. We want to find the number of wins that Jeff Pfeffer (we know his code name is pfeffje01) got in 1915, but we don't know which league he pitched in.

The first step is to give the worksheets a name range. In this example the three worksheet names are listed in column A and we give them the name 'baseball'. Just select the cells with the worksheet names and enter the name in the box on the left of the toolbar.

In the array formula we refer to the range name twice, the value we're searching for at the beginning after 'VLOOKUP(' and then again in the middle. The 6 at the end represents the value in the array that we to pull. The number of wins is listed in the sixth column.

We're searching the same cell range on each worksheet A1:T200, and the value we're looking for must appear in column A of one of the worksheets.