Email:      Pass:    Pass?


Advertisements


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?


Similar Excel Video Tutorials

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
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li

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 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.

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="Non-Perforated",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.




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.

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




Hi,

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.

Thanks!

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




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
ExplanationOfProblem.xlsx

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,
Sandra

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 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?

SHEET - ONE DATA DUMP
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
formula.
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.

Hlookup.xlsx

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 right-most Column of a table (Vlookup). Attempting not to have to create possible "dummy" columns.