Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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 ...
Vlookup on Dates and Times in Excel
Perform a lookup on dates and times in Excel: vlookup, hlookup, index/match, any kind of lookup. This tutorial show ...
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 ...

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







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?


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.


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




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






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?




Hi

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'm supposed to use a VLOOKUP function to show the cost of yardwork on a small, medium, large and commercial sized lawns. There is a table in another sheet that shows these costs. I can't get the VLOOKUP to work, I got it to work with a nested IF function but I'm supposed to use VLOOKUP. When I try to show the total cost with discounts, using the HLOOKUP from another table, I only get "VALUE" errors.

Thanks!

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


I was reading the comments from the below topic and was very close to answering the question I had... but I need it to go a step further.

http://www.mrexcel.com/board2/viewto...lookup+vlookup


I have a table with the following layout:

1) Column A has an ID # (with same ID appearing on different rows)
2) Row one is the heading with a letter and can repeat across multiple columns.

I want to create a sumif using a vlookup and a hlookup, but I don't know how to combine the two and get the formula to work. For example in the list below, I want to sum everything that has ID6 and the columns with a B header.... I know this can be done using pivot tables, but I am trying to avoid this.........Please help! thanks

ID# A A B C A B
ID1 8 5 1 1 4 9
ID2 9 0 2 5 9 9
ID6 9 1 2 8 2 8
ID4 2 7 9 0 8 0
ID5 4 7 5 5 6 9
ID6 1 9 2 7 7 8
ID7 4 4 8 5 5 0
ID8 5 0 6 9 0 2
ID6 8 1 3 9 4 0
ID2 6 5 9 5 4 4
ID1 8 8 1 1 5 5
ID6 6 1 7 8 1 9
ID6 1 8 1 0 3 4
ID2 3 0 6 7 7 6
ID1 9 1 5 4 1 0
ID2 9 9 2 2 1 2
ID1 8 9 4 5 6 7
ID2 9 0 7 1 4 2
ID6 6 9 0 9 1 6
ID2 7 8 3 8 3 2


I am having difficulty with the code below:

Code:

=IF(AND(LEN($C2)=3,LEFT(HLOOKUP($A$1,$A:$A,$D2+I$1,1),3)=$C2),CONCATENATE($B2,HLOOKUP($A$1,$A:$A,$D2+I$1,1)),IF(AND(LEN($C2)=5,LEFT(HLOOKUP($A$1,$A:$A,$D2+I$1,1),5)=$C2),CONCATENATE($B2,HLOOKUP($A$1,$A:$A,$D2+I$1,1)),IF(AND(LEN($C2)=6,LEFT(HLOOKUP($A$1,$A:$A,$D2+I$1,1),6)=$C2),CONCATENATE($B2,$C2),IF(AND(LEN($C2)=7,OR($D2+I$1<=$E2,LEFT(HLOOKUP($A$1,$A:$A,$D2+I$1,1),3)=RIGHT($C2,3))),CONCATENATE($B2,HLOOKUP($A$1,$A:$A,$D2+I$1,1)),IF(AND(LEN($C2)=11,OR($D2+I$1<=$E2,LEFT( HLOOKUP ($A$1,$A:$A,$D2+I$1,1),5)=RIGHT($C2,5))),CONCATENATE($B2,HLOOKUP($A$1,$A:$A,$D2+I$1,1)),IF(AND(LEN($C2)=13,$D2+I$1<=$E2),CONCATENATE($B2,HLOOKUP($A$1,$A:$A,$D2+I$1,1)),""))))))


The above is entered in a single cell, C2 is seven characters long, and when I press enter, I get an "Error" dialog box and the above highlighted HLOOKUP is highlighted black to presumably indicate it's the problem area. If anyone has any tips for this novice user, I would greatly appreciate it. Thanks.


Hi all
Hope you can help me. I am trying to combine lookup statements with IF statements but I am not having much joy. Below is where I am at the moment but obviously does not work

=IF(D8="","",IF(D8>16,HLOOKUP($D8,'Boys Decs'!$B$20:$Q$42,3,0),VLOOKUP('Boys Decs'!$A$44:$C$49,2,0)))


The lookup value (D8) is a number. If it is less than 16 I want the HLOOKUP to work and if not I want the VLOOKUP to work.

Hope someone can help me
Thanks


Hi,

I thought (and still do) that hlookup were different to vlookup only by looking up values in rows and not columns(?).

Allthough by wiriting this formula I get a #N/A error;

=HLOOKUP(A2,'C:\Sample\[Samplefile.xls]Sample'!$B$1:$IV$7,7,FALSE)

I am looking up dates in row 1 (according to A1), and give the values in row 7.

Anyone got any ideas on why I get error message and want to share them with me

Gnoke


Hi All,

This is just a small snapshot of a larger table. Is there something that will work as a replacement for this Hlookup formula so I can avoid having to type in 2,3,4,5 etc. in the third argument of the formula.

PWLF 1

* P Q R S T U V 2 WLF2 WLF1 WLF2 WLF3 WLF4 WLF5 WLF6 3 4,773 6,639 4,773 4,143 6,045 2,059 3,469 4 3,589 4,662 3,589 5,609 4,176 2,866 6,211 5 5,581 2,034 5,581 1,531 2,976 3,662 4,062
Spreadsheet Formulas Cell Formula P3 =HLOOKUP($P$2,$Q$2:$V$5,2,FALSE) P4 =HLOOKUP($P$2,$Q$2:$V$5,3,FALSE) P5 =HLOOKUP($P$2,$Q$2:$V$5,4,FALSE)

Excel tables to the web >> Excel Jeanie HTML 4


Urgent.

Can someone provide me with examples of various nested vlookup/hlookup formulas.
Any document/links.............. etc will do.

thanks


I'm using the HLOOKUP formula to return a value in the sheet called "InputSheet". Here is the formula:

=HLOOKUP($A4, InputSheet!$C$2:$J$23,4,FALSE)

It returns the value in C5.

Is there a way to modify this formula so that it returns the value in D5 (the value one column to the right of the "hit" in HLOOKUP)? Perhaps some combination of HLOOKUP and OFFSET.

Many thanks in advance!


I have a defined name where the range is taken from another workbook (Contract1).


If I use the following: =HLOOKUP(A1,Contract1,3,False) I get the correct return...

If I use =HLOOKUP(TL,C1,AG1,FALSE) I get a #N/A error (where C1 = Contract1 following a vlookup from elsewhere.

Any ideas?

Kind Regards.


How do I return the value of a cell that is found by using the value in A1 and A2. For example A1 = 3 and A2 = 4, the value that is returned would be that in Column 3 Row 4?

I've found Vlookup and Hlookup but need to use both.

Thanks again.


Hi,

I have a spread sheet with data that I get from an external site.. See attached image.


How can i write a formula that can pull out the rightmost column of data... for a specific year. E.g. I want a formula that can pull out the cell value for Year Y and Month X.

Can I use a nested VLOOKUP and HLOOKUP? Where the array for the HLOOKUP depends on cell ref for the VLOOKUP cell of the year... Or do I need to write a macro for this?

I hope I made myself understandable..


Hello,

Is there a way when i can incorporate both hlookup and vlookup to find a cell. This is kinda like a graph where you have coordinates, i want to target a specific cell. I want to use this because I end up having to change the original lookup formula everytime i add a row


I am getting issues with cells everytime i add a row. I need to find a way to fix that, so it will stay the same, regardless when i add.




any help would be greatly appreciated.