Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Can Vlookup Hlookup Work For Different Sheets?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi! Just want to ask if Vlookup and Hlookup works for different sheet but same workbook? What would the formula be?

View Answers     

Similar Excel Tutorials

HLOOKUP in Excel
The Hlookup function allows you to scan a row from left to right in search of a value and then return the contents ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
Vlookup on Data with Spaces
How to use the Vlookup function when your data has extra spaces in it. This happens when the lookup data is import ...
Vlookup with a Range of Numbers in Excel
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...

Helpful Excel Macros

Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the

Similar Topics

Hi, i was wondering if there is a way to make an or formula to return the value instead of the true statemnet

In any case here is the formula i used, it works properly but i need to get the value instead of the "TRUE" statement

Any one has any idea: Thanks in advance


each of the 3 parts of the or il will return a value if used individually.

Excel 2002

I have a number (22.3) in a cell that is there from an Hlookup function. I am then using a vlookup to lookup the 22.3 (from the hlookup) in a column. The number is in the column, but the vlookup function cant find it. If I were to type over my hlookup formula so that I am hand-typing in "22.3" instead of it being their from the hlookup, then the vlookup works just fine. I hope that is not too confusing. I dont understand what is going on. I checked to make sure none of my cells are rounding and therefore creating a look-alike number when it really isn't and that is not the case. Any ideas would be greatly appreciated.


Hello All,
This is my first post so forgive me if this seems too obvious.

I have a Broad table of data that I would like to be able to pull data from. Within my spreadsheet I have two cells that can be used to identify the column and row within the table.

the formula that I've been trying to use is:

The trouble I"m having is that sometimes my formula seems to work just fine, and other times the formula gives me a #N/A value, and I'm not sure why. When I run the "evaluate formula" It appears that the correct value is obtained from the Hlookup function, but the Vlookup produces #N/A.

Any suggestions?

Hi i would like to use hlookup/vlookup to auto fill a cell. Based on a account number i would like it to fill the account name from a different sheet.

Dont really understand vlookup/hlookup

Simon Green

I'm trying to get an average with hlookup..

i'm trying to make the average ignore if vlookup doesn't find anything

tried to use a blank space "", but it will retunr #VALUE! Error..

What can i do?

this is the formula, and i need to use it from Jan to Dec, so it will be waay longer than this, any better idea to do this?

=AVERAGE((IF(ISERROR(+HLOOKUP(C4,Jan!D7:M23,17,0))=TRUE,0,(+HLOOKUP(C4,Jan!D7:M23,17,0))),IF(ISERROR(+HLOOKUP(C4,Feb!D7: M23,17,0))=TRUE,"",(+HLOOKUP(C4,Feb!D7:M23,17,0)))))

I am currently working on rebuilding a file, that tracks units shipped by date, to a upc. The UPC, will run down column A, and the dates as column headers in row one. The problem I am having is that the units shipped are tracked dailey in one system, and I need to report them weekly in the other. I am basically summing all itterations of the same week, (YYYYWK) and returning them tio the aoppropriate column(week) and upc, Row.

Here is what I have been trying to do, as of right now I have a max of 4 occurances, but could have 7, [B$1]

=IF(B$1=3,SUM((VLOOKUP($A3,'ship data'!$A:$HY,(SMALL(HLOOKUP(B$2,'ship data'!1:1048576,831,FALSE),1)),FALSE)),+(((VLOOKUP($A3,'ship data'!$A:$HY,(SMALL(HLOOKUP(B$2,'ship data'!1:1048576,831,FALSE)+1,1)),FALSE)))),+(((VLOOKUP($A3,'ship data'!$A:$HY,(SMALL(HLOOKUP(B$2,'ship data'!1:1048576,831,FALSE)+2,1)),FALSE))))),(IF(B$1=2,SUM((VLOOKUP($A3,'ship data'!$A:$HY,(SMALL(HLOOKUP(B$2,'ship data'!1:1048576,831,FALSE),1)),FALSE)),+(((VLOOKUP($A3,'ship data'!$A:$HY,(SMALL(HLOOKUP(B$2,'ship data'!1:1048576,831,FALSE)+1,1)),FALSE)))))),(IF(B$1=1,SUM((VLOOKUP($A3,'ship data'!$A:$HY,(SMALL(HLOOKUP(B$2,'ship data'!1:1048576,831,FALSE),1)),FALSE))))),(IF(B$1=4,SUM((VLOOKUP($A3,'ship data'!$A:$HY,(SMALL(HLOOKUP(B$2,'ship data'!1:1048576,831,FALSE),1)),FALSE)),+(((VLOOKUP($A3,'ship data'!$A:$HY,(SMALL(HLOOKUP(B$2,'ship data'!1:1048576,831,FALSE)+1,1)),FALSE)))),+(((VLOOKUP($A3,'ship data'!$A:$HY,(SMALL(HLOOKUP(B$2,'ship data'!1:1048576,831,FALSE)+2,1)),FALSE)))),+(((VLOOKUP($A3,'ship data'!$A:$HY,(SMALL(HLOOKUP(B$2,'ship data'!1:1048576,831,FALSE)+3,1)),FALSE))))),0))))

I got this formula in my worksheet at the moment

Main Page

AI AJ AK AL AM AN AO 4 19/10 20/10 21/10 22/10 23/10 24/10 25/10 5 Mon Tue Wed Thu Fri Sat Sun 6 23 22 22 21 23 14 11 7 #N/A #N/A #N/A #N/A #N/A #N/A #N/A 8 1 1 1 1 1 1
Spreadsheet Formulas Cell Formula AI8 =IF(NOT (VLOOKUP ($A8,Monday!$A$4:$BZ$46, (HLOOKUP ('Main Page'!AI$4,Monday!$A$1:$BZ$2,2) ) ,FALSE) ="OFF") ,1,"") AJ8 =IF(NOT (VLOOKUP ($A8,Tuesday!$A$4:$BZ$42, (HLOOKUP ('Main Page'!AJ$4,Tuesday!$A$1:$BZ$2,2) ) ,FALSE) ="OFF") ,1,"") AK8 =IF(NOT (VLOOKUP ($A8,Wednesday!$A$4:$BZ$42, (HLOOKUP ('Main Page'!AK$4,Wednesday!$A$1:$BZ$2,2) ) ,FALSE) ="OFF") ,1,"") AL8 =IF(NOT (VLOOKUP ($A8,Thursday!$A$4:$BZ$42, (HLOOKUP ('Main Page'!AL$4,Thursday!$A$1:$BZ$2,2) ) ,FALSE) ="OFF") ,1,"") AM8 =IF(NOT (VLOOKUP ($A8,Friday!$A$4:$BZ$42, (HLOOKUP ('Main Page'!AM$4,Friday!$A$1:$BZ$2,2) ) ,FALSE) ="OFF") ,1,"") AN8 =IF(NOT (VLOOKUP ($A8,Saturday!$A$4:$BZ$42, (HLOOKUP ('Main Page'!AN$4,Saturday!$A$1:$BZ$2,2) ) ,FALSE) ="OFF") ,1,"") AO8 =IF(NOT (VLOOKUP ($A8,Sunday!$A$4:$BZ$42, (HLOOKUP ('Main Page'!AO$4,Sunday!$A$1:$BZ$2,2) ) ,FALSE) ="OFF") ,1,"")

Excel tables to the web >> Excel Jeanie HTML 4

It is working fine, but at the moment, I have to type in manually the Sheet Names in the formulas in different columns. Is it possible to have the formula pick up a Sheet name depending on the value of a cell for example AI5 is a Mon, so formula in AI8 should be able to pick up Monday as a the sheet name.

Dear All
I am trying to produce a report that requires a mixture of VLookup and HLookup in the same formula.
I need to lookup one value in an array and return the value in a column that matches another value.
I have attached an example workbook to explain further.
Any help gratefully received.


I am trying to categorize values based on years and levels...each year has a different set of criteria...for example:


2004 30 (result would be A)
2005 50 (result would be B)

in table:

2002 10 20 30 40 50
2003 20 30 40 50 60
2004 30 40 50 60 70
2005 40 50 60 70 80

I tried to use VLOOKUP to locate a) the data set for the particular year and b) the number of rows to return an HLOOKUP value:

=HLOOKUP(value,VLOOKUP(to lookup value for row to return HLOOKUP value),VLOOKUP(to lookup row for the right year))

but it didn't there a correct way to look up these values? thanks in advance


I'm not sure if I need a vlookup and and hlookup in a single formula.

I have attached a workbook. In short I have two tables and the cells highlighted in yellow need to pull the correct figures from the above table. Basically it need to read off an intersection.

Any help would be appreciated.


I need to populate a table but am trouble figuring out how to get it work with vlookup or hlookup. It seems simple but, darn, I got stuck. Attached is the sample and I appreciate any suggestions.

Is there a way i can use a hookup or a vlookup to combine the data of many rows.

For instance if i use a hlookup to look up a legend, and the legend has 3 rows of text can i use a hlookup to display all 3 rows of text? must be some way to use a hlookup or any other means to combine rows .

Basically the hlookup lookups finds a legend in column A, and needs to display whats in column B , but the data in B are in 3 rows not just 1.


i was thinking something like this but it didnt work, it looks up GQ19, and shows the results in row 2, i added & 3 & 4 just to see if it could show row 2 3 and 4 but it didnt work.



I am trying to look up values using if condition combined with vlookup and hlookup. The values are in a separate workbook/worksheets to the target workbook/worksheet.

Below is the formula which I have at the moment. But I get 'N/A' error.



Hi everyone,

I have got an issue. In the file enclosed, you will see that I would like to input the data from the left tab into the right tab. I have tried with a vlookup, a hlookup (but with the difference of dispositon, it's not possible) or even a pivot table but it seems it does not work. How would you manage to do that ? Thanks a lot.



Pls help me out by providing a formula to nest Vlookup & Hlookup. Herewith enclosed some datas to workout.

Pls do the needful.

Thanks in advance.


Dear All,

basically I have two questions, details in my excel sheet:
- why does my vlookup not work in the sheet
- how can I combine vlookup (or hlookup) with selected sums

it would be great if you could help me to solve this with a formula, rather than PIVOT or changing the layout a lot. there is quite a large original file and I have not many options to change the layout..

thanks a lot!

kind regards,

Hi Everyone,

I have two sheets currently. One is a Data Dump (on the bottom). The other is "sheet4" on the top where I just need to be able to match the column with the row (Vlookup/HLookup). Is there a vlookup/hlookup combo formula that can get me to the answer I want? Note that my Rows consist of dropdown menus/dates that change accordingly.. Please see below.

Sorry for entering it here instead of a real worksheet. I can't seem to attach a file small enough to be able to be posted on this website!!

Thank you very much!!!

ENTER DATE ENTER DATE Name of Manager May-11 Jun-11 Total Plan (9/30/90) Total Fund Benchmark Looking to be able to go to "Sheet ONE DATA DUMP below and get a match of -1.3. How do I get a vlookup and hlookup formula to get this? Looking to be able to go to "Sheet ONE DATA and get a match of -1.5. How do I get a vlookup and hlookup formula to get this?

May-11 Jun-11 Total Plan (9/30/90) -1.1 -1.0 Total Fund Benchmark -1.3 -1.5 Mstar Moderate Allocation Fund -0.8 -1.4 CPI Plus 5% 0.7 0.2

I have a table which is static that contains figures from all months of the
year for individual items.
J F M A M J.....
Item 1 10 20 15 10 5 7
Item 2 7 58 45 25 4 24

If on another table (which is dyanmic - the months will change each quarter)
Month 1 Month 2 Month 3
Item 1 ________ ________ ________
Item 2 ________ ________ ________

I want the underlined cells to reference the same item from the other table
for whichever month is displayed in the 'month1" cell. I assume that i
need to use both the vlookup and hlookup functions because i want it to look
up the "item" through vlookup and the month with hlookup, but i am not
familiar with how the hlookup works or how to combine the two into the same
Can anyone help me with this? Thanks.

Attached is a spreadsheet I'm using to track winnings and fantasy points for my fantasy football league. I'd like to add a Top 10 Weekly Performance section for each category (Total FPS, QBs, RBs, WRs, TEs, DSTs), but I'm having no luck with HLOOKUP and VLOOKUP - two functions I thought I understood. I'm using LARGE to grab the top 10 scores in each category, but using HLOOKUP to add the week value and using VLOOKUP to add the coach value is not working. Maybe there is a better way or maybe I'm missing something about the look up functions? Any suggestions would be greatly appreciated.

Hi, first of all, I would like to thank all of you for helping me so much throughout the weeks I have been on here and would really like to say slowly but surely I am learning excel.

I have a sample workbook I will put at the end, but I need someway for hlookup to work overall even if one of the hlookups doesn't.

IE (this is from the sample workbook): =HLOOKUP("A",Sheet2!A1:C1,2,0)+HLOOKUP("A",Sheet3!A1:C3,2,0)+HLOOKUP("A",Sheet4!A1:C3,2,0).

If "A" isn't in one of the sheets no matter what it will return #N/A. Is there some way (I'm sure there is) for me to make these #N/A hlookups just act as zero?

I have a Hlookup but then in the array some cells are merged. See attachment.
I tried Hlookup for 02/09 but gives me 0. When I do Hlookup for 01/09 it works. Something with Index Match? Thanks. M.


Understanding the basic method and setup used for VLookup and HLookup, has anyone found a way to use them when the LOOKUP variable ie either the last Row of a table (HLookup) or the right-most Column of a table (Vlookup). Attempting not to have to create possible "dummy" columns.


Actually i want to create a function that will produce result like Hlookup by using Vlookup but failed please if any body knows how to do this please advice.

Hi, I'm trying to do a HLOOKUP from a different workbook, basically it references dates on both sheets. Now I have noticed that on the sheet that the HLOOKUP formula is in, the date 20/08/2007 when not formatted to date is 37852, whereas the same date in the workbook being referenced is 39314, so the HLOOKUP is not working? why are the 2 dates different? If I open a new workbook the date is 39314. so i am assuming this is the correct figure. Why is my one workbook largely different?

Thanks in advance

I have the data in same sheet.

What i want to do is

From hlookup i want to use the vlookup of that date of anyother method against which i get the value . the data is raw dump format and i just cant seem to get it work .

hlookup is not working when i select the entire column but it works when i select the cell .

Situtation :

From the Sample Sheet .

There is Raw Data in Column A . This Data can be in any cell but this is sure that Value will only be in Column A but cell location in A column can be different.
I want to properly organize the data , keeping in view that whenever i enter the data in that cell the finish data pick the data automatically and organize it properly.
Wat i m facing problem is if the value in Cell A matches with the Example 1 from the Finish data and if the Date matches with the date of finish column i want the Value to be reflected in Date cell in finish data . i tried every possible way to do but i just cant seem to get it work , i tried hlookup , vlookup and all.

the condition is formula should be not be restricted to particlar range the it should either be infinite or atleast working on max 50000 rows . because the data is large . i tried index match also but still not hope

the value should accurately be entered in cell in finish data for that Particular example 1only! but not mixing with another one. please advice. i tried pivot technique also cant seem to work .

In Short : If the Colum A has the data which is present in Finish Data , it matches with the Example 1 and Date for Example 1 and the show the value of example 1 date value 1 and so goes on .

Please also share the excel with calculation

sample sheet can be downloaded from