CAN VLOOKUP SEARCH AN ENTIRE WORKSHEET INSTEAD OF RANGE OF CELLS
 

WHEN I COPY A VLOOKUP FORMULA THE CELL RANGE IS AUTOMATICALLY ADJUSTED. IS
THERE A WAY TO USE VLOOKUP WITH AN ENTIRE WORKSHEET AS THE RANGE THAT I WANT
IT TO SEARCH AS OPPOSED TO REFERENCING A SERIES OF INDIVIDUAL CELLS?

Answer: CAN VLOOKUP SEARCH AN ENTIRE WORKSHEET INSTEAD OF RANGE OF CELLS
 

Yes, VLOOKUP can search an entire worksheet instead of a range of cells. To do this, you can use the following steps:

1. Click on the cell where you want to enter the VLOOKUP formula.
2. Type the formula "=VLOOKUP(" without the quotes.
3. Click on the cell that contains the value you want to look up.
4. Type a comma "," without the quotes.
5. Click on the worksheet tab that contains the data you want to search.
6. Click on any cell in the worksheet to select it.
7. Type a colon ":" without the quotes.
8. Press the "Ctrl" key on your keyboard and click on the last cell in the worksheet that you want to search.
9. Type a comma "," without the quotes.
10. Enter the column number that contains the data you want to return.
11. Type a closing parenthesis ")" without the quotes.
12. Press "Enter" on your keyboard to complete the formula.

For example, if you want to search the entire worksheet named "Data" for the value in cell A1 and return the value in column B, the formula would be:

Note that the "FALSE" at the end of the formula is used to indicate an exact match. If you want to allow for approximate matches, you can use "TRUE" instead.

Also, when you copy the formula to other cells, the cell range will automatically adjust based on the relative position of the cells. However, the worksheet reference (e.g. "Data!A:B") will remain the same.

CAN VLOOKUP SEARCH AN ENTIRE WORKSHEET INSTEAD OF RANGE OF CELLS
 

if you're =vlookup() formula looks like:

=vlookup(a1,sheet2!a1:b9,2,false)
you could use
=vlookup(a1,sheet2!$a$1:$b$9,2,false)
so that $a$1:$b$9 range won't change if you copy/move that formula.

But you could also do something like:
=vlookup(a1,sheet2!a:b,2,false)
(if there's nothing under that table that would bother the =vlookup() formula)


BROCK8292 wrote:

WHEN I COPY A VLOOKUP FORMULA THE CELL RANGE IS AUTOMATICALLY ADJUSTED. IS
THERE A WAY TO USE VLOOKUP WITH AN ENTIRE WORKSHEET AS THE RANGE THAT I WANT
IT TO SEARCH AS OPPOSED TO REFERENCING A SERIES OF INDIVIDUAL CELLS?

--

Dave Peterson

CAN VLOOKUP SEARCH AN ENTIRE WORKSHEET INSTEAD OF RANGE OF CEL
 

THANK YOU, THAT WAS VERY HELPFUL
WHEN I WAS SENT THIS FORMULA IT LOOKED LIKE THIS
=VLOOKUP(A1,WORKSHEET NAME,4)
BUT MY FORMULA IS IDENTICAL TO WHAT YOU SENT ME W/O THE $
DO YOU KNOW WHY THE FIRST FORMULA WILL ONLY WORK IN THE WORKSHEET IT WAS
SENT TO ME IN AND NOT ONE THAT I CREATE?

"Dave Peterson" wrote:

if you're =vlookup() formula looks like:

=vlookup(a1,sheet2!a1:b9,2,false)
you could use
=vlookup(a1,sheet2!$a$1:$b$9,2,false)
so that $a$1:$b$9 range won't change if you copy/move that formula.

But you could also do something like:
=vlookup(a1,sheet2!a:b,2,false)
(if there's nothing under that table that would bother the =vlookup() formula)


BROCK8292 wrote:

WHEN I COPY A VLOOKUP FORMULA THE CELL RANGE IS AUTOMATICALLY ADJUSTED. IS
THERE A WAY TO USE VLOOKUP WITH AN ENTIRE WORKSHEET AS THE RANGE THAT I WANT
IT TO SEARCH AS OPPOSED TO REFERENCING A SERIES OF INDIVIDUAL CELLS?

--

Dave Peterson

CAN VLOOKUP SEARCH AN ENTIRE WORKSHEET INSTEAD OF RANGE OF CEL
 

If that's the way the formula looks (just the worksheet name), then it really
isn't used as a worksheet name--it's just coincidence that someone used:

Insert|Name|define
and decided to use the name of an existing worksheet.

My guess is that you didn't create that name in the new workbook.



BROCK8292 wrote:

THANK YOU, THAT WAS VERY HELPFUL
WHEN I WAS SENT THIS FORMULA IT LOOKED LIKE THIS
=VLOOKUP(A1,WORKSHEET NAME,4)
BUT MY FORMULA IS IDENTICAL TO WHAT YOU SENT ME W/O THE $
DO YOU KNOW WHY THE FIRST FORMULA WILL ONLY WORK IN THE WORKSHEET IT WAS
SENT TO ME IN AND NOT ONE THAT I CREATE?

"Dave Peterson" wrote:

if you're =vlookup() formula looks like:

=vlookup(a1,sheet2!a1:b9,2,false)
you could use
=vlookup(a1,sheet2!$a$1:$b$9,2,false)
so that $a$1:$b$9 range won't change if you copy/move that formula.

But you could also do something like:
=vlookup(a1,sheet2!a:b,2,false)
(if there's nothing under that table that would bother the =vlookup() formula)


BROCK8292 wrote:

WHEN I COPY A VLOOKUP FORMULA THE CELL RANGE IS AUTOMATICALLY ADJUSTED. IS
THERE A WAY TO USE VLOOKUP WITH AN ENTIRE WORKSHEET AS THE RANGE THAT I WANT
IT TO SEARCH AS OPPOSED TO REFERENCING A SERIES OF INDIVIDUAL CELLS?

--

Dave Peterson


--

Dave Peterson