
Can Vlookup Hlookup Work For Different Sheets?


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Can Vlookup Hlookup Work For Different Sheets?  Excel

View Answers


Hi! Just want to ask if Vlookup and Hlookup works for different sheet but same workbook? What would the formula be?
Similar Excel Video Tutorials
HLOOKUP Function
 See how to use the HLOOKUP function! We all know how to use VLOOKUP, but how do we use HLOOKUP? See how to use HLOOKUP in this video. In Th ...
VLOOKUP w 3 Tables on Diff Sheets
 See how use VLOOKUP when there are 3 tables on 3 different sheets. See how to use the VLOOKUP & INDIRECT functions as well as named cell ranges in ...
VLOOKUP Function 5th Example
 See how to create an array in a VLOOKUP formula, and see how to have the VLOOKUP function look at a table on a different worksheet than the one the VL ...
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
=OR(IF($D$2="Low",IF($D$3="ALL",VLOOKUP($B7,sav,3,0)*HLOOKUP($D$1,geo,4,0),IF($D$3="NA",VLOOKUP($B7,sav,3,0)*HLOOKUP($D$1,geo,2,0),VLOOKUP($B7,sav,3,0)*HLOOKUP($D$1,geo,3,0))),0),IF($D$2="Aggressive",IF($D$3="ALL",VLOOKUP($B7,sav,4,0)*HLOOKUP($D$1,geo,4,0),IF($D$3="NA",VLOOKUP($B7,sav,4,0)*HLOOKUP($D$1,geo,2,0),VLOOKUP($B7,sav,4,0)*HLOOKUP($D$1,geo,3,0))),0),IF($D$2="Expected",IF($D$3="ALL",VLOOKUP($B7,sav,5,0)*HLOOKUP($D$1,geo,4,0),IF($D$3="NA",VLOOKUP($B7,sav,5,0)*HLOOKUP($D$1,geo,2,0),VLOOKUP($B7,sav,5,0)*HLOOKUP($D$1,geo,3,0))),0))
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 handtyping 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 lookalike number when it really isn't and that is not the case. Any ideas would be greatly appreciated.
Thanks
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:
=IF(C29="NonPerforated",1,VLOOKUP(G34,P35:AG125,HLOOKUP($G$35,$P$32:$AG$34,3,FALSE),FALSE))
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
Thanks,
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.
There are three choices for a number for a formula. They are a follows;
1. Direct input of a number
2. HLOOKUP to supply the number
3. Another HLOOKUP to supply another number
All or any one may be used. The HLOOKUP feature works well. When used they both return the proper numbers from a charts. If I use both the HLOOKUP's and input a number it works OK. If I use just the HLOOKUP's and do not input a manula number it works OK. The problem is when I do not use both HLOOKUP's.
I would like to add all three fields together to run a formula. The number will be used in another formula.
For example
(B3+C4+G5)20 C4 & G5 is the results of the HLOOKUP. All three fields do not always require an input. If one of the HLOOKUP fields are left blank I get a #NA in the final formula.
How do I get the HLOOKUP to supply me with a zero or another idea, if not used in the equation?
Thanks
Hello:
I am trying to categorize values based on years and levels...each year has a different set of criteria...for example:
categorize:
2004 30 (result would be A)
2005 50 (result would be B)
in table:
A B C D E
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 work...is there a correct way to look up these values? thanks in advance
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.
=HLOOKUP(GQ19,Verbiage!$1:$65536,(2&3&4),FALSE)
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.
Thanks
Hi,
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.
=IF(HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,10,FALSE)="Worker",
VLOOKUP($A2,'C:\ABC\[Workbook1.xlsm]WorkerNames'!$B$2:$Z$100,5,FALSE),IF(HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,10,FALSE)="Employer",HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,11,FALSE),IF(HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,10,FALSE)="Provider",HLOOKUP($B2,'C:\ABC\[Workbook1.xlsm]Payment.D1'!$B$2:$Z$100,11,FALSE))))
Jet
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.
Arnaud
Hi,
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.
Regards,
Arun
Hi,
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.
Regards,
Arun
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!!!
SHEET 4
ENTER DATE
ENTER DATE
Name of Manager
May11
Jun11
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?
SHEET  ONE DATA DUMP
May11
Jun11
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
formula.
Can anyone help me with this? Thanks.
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?
Hi All,
I am trying to import a BASE ESTIMATE table into EXCEL.
I have problems with most of the formulas, especially this one:
=VLOOKUP($E$2,$B$24:$P$604,HLOOKUP($E$3,$D$22:$L$604,1)+2)*HLOOKUP(HLOOKUP($E$3,$D$22:$L$604,1),$D$2 2:$L$23,2)
and this one
=ROUND((IF(AND(OR(E7>E5,E7>E6),E3<40000),E7,IF(AND(E6<E5,E3<40000),E6,E5)))*E8*1.055*1.06,2)+10
I am not sure if EXCEL allows a HLOOKUP within an HLOOKUP. If not, how can I get around this?
Thank you so much in advance!
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 rightmost Column of a table (Vlookup). Attempting not to have to create possible "dummy" columns.
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
http://www.4shared.com/file/24523277.../1_online.html
Hi there,
I am having problems with hlookup (or maybe not?)
I am trying to get hlookup to do something similar to vlookup. Only looking up and down and not along the row...
I want to look up a cell in sheeta then display the contents of the cell directly below it back in sheetb.
Please help me ?
Hello all,
I've been struggling with finding a solution to this problem. I have the individual pieces correct, but when I try to combine them I have issues.
Currently I have 2 sheets. 1 is an entry sheet in which a plant operator would indicate if bins are empty, hung, or have nonflowable inventory. These are just records in cells.
We have another "Journal" sheet, which simply uses vlookups to reflect the bin situation over the course of a year. The bin number is listed along the left, the month along the top, which forms a grid.
Here's the formula used:
Code:
=IF(ISNA(VLOOKUP("Bin"&$A5&"Month"&F$3,Entry!$Q:$S,3,FALSE)),VLOOKUP("Bin"&$A5&"Month"&F$3,Entry!$A:$S,19,FALSE),"Empty")
However, I also need the sheet to evaluate itself for HANGS. If there's a hang, in say, February, I need that HANG to continue forwards to whatever the current month is, unless a different condition interrupts that (IE: a bin gets an empty). I created an hlookup
Code:
=HLOOKUP("Hang",E5:P5,1,FALSE)
to do this for me, but I'm not sure how to combine this formula and the formula above together so they work properly.
Am I going about this the right way?
Hello all,
I am currently using HLookup to fill a few cells which will in turn be used to create a pie chart. The whole thing works except when there are 2 identical values.
AB
=Large(rng,1) =Hlookup(A1,A5:V10,5,FALSE)
=Large(rng,2) =Hlookup(A2,A5:V10,5,FALSE)
There are times that the highest value (A1) will be equal to the second highest value (A2). When this happens, the Hlookup will display the same thing twice. I know that this is just how the Lookups work but is there a work around for this?
I hope I was clear enough

