How to find values on another worksheet.
Find data on other worksheets
-
Add the name of the worksheet and an exclamation point to the table array argument, like so:
=VLOOKUP(H2,Pages!B3:F25,3,FALSE)
Want more?
If you need to, you can lookup values on more than one worksheet in a workbook.
As an example, we downloaded some website data from a database.
The first sheet has Page Numbers for the site pages, and the Hits on each page.
The second sheet also has the Page Numbers, plus the Page Names.
VLOOKUP can help me aggregate this data, so I can see Page IDs, Names, and Hits in one place.
I'll start here on the Pages tab, because it gives me two of the function's arguments.
First, I see the Page Names are listed in column B, the second column over.
This is what I want VLOOKUP to find, the answers I want to see, so my column reference argument is 2.
Also, I know I want to find the Page Name that matches a given Page Number.
That data starts in cell A2 and ends in cell B39, so A2 to B39 is my lookup table argument.
Now, I'll go to the Page Views tab, enter a couple of headings, and my function.
Equal sign, VLOOKUP, open parentheses, and F2 as my lookup value because that's where I'll enter the Page IDs.
Next, I enter Pages, the name of the other tab, an exclamation point, then my lookup table, a comma, and my column reference.
In other words, the lookup table argument is now the name of the worksheet, the exclamation point, and the range of cells I want to search.
Because I need exact matches, I'll enter FALSE as my range lookup argument.
Press Enter to finish the formula, and you can see I get an error message because the formula wants a value in cell F2.
So, I'll Copy a Page ID here, Paste it, and now I know how many Hits National Page News 2 received.
To do this again, press Esc to clear the marquee, Copy another Page Number, Paste it, and you see another Page Name.
Next, I'll show you how to use absolute cell references in VLOOKUP formulas. They are how you copy the formula down a column.
No comments:
Post a Comment