Hlookup!?! 


Hlookup!?!  Excel 
View Answers 
Hi,
I have the following formula and cannot understand what the (11) means.
Can anyone help me?
Thanks
=HLOOKUP($B$3,Forecasts!$R$12:$BE$317,ROW(Moalajah)11,FALSE)
I have the following formula and cannot understand what the (11) means.
Can anyone help me?
Thanks
=HLOOKUP($B$3,Forecasts!$R$12:$BE$317,ROW(Moalajah)11,FALSE)
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 ...
In Th ...
VLOOKUP 1 item & Add Values from Multiple Rows/Columns  HLOOKUP
 See how to do 1 Condition Lookup Adding for both the VLOOKUP and HLOOKUP functions. See how to use an array constant in the row/column num argument fo ...
HLOOKUP ROWS Horizontal Lookup
 Learn how to use the HLOOKUP and ROWS function to retrieve multiple values from a Horizontal table.
This is a beginning to advanced Excel c ...
This is a beginning to advanced Excel c ...
CHOOSE function
 See how to use the CHOOSE function to select an item from a list. This function is useful if you want to type the lookup table into the function itsel ...
Helpful Excel Macros
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
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
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
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
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Format Cells as a Scientific Number in Excel Number Formatting
 This free Excel macro formats selected cells in the Scientific number format in Excel. This means the cell will be put
 This free Excel macro formats selected cells in the Scientific number format in Excel. This means the cell will be put
Complete Guide to Printing in Excel Macros  PrintOut Method in Excel
 This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
 This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Similar Topics
Hi,
I have built a fairly extensive financial model for evaluating drug trials. The model links three separate workbooks together.
I am now trying to replicate the model for a separate set of drug trials, which has necessitated saving the three workbooks under new names  but they all still link to one another (and when I update the links, this is confirmed).
Some of the formulae in the financial model return the #N/A value, but when I evaluate the formulae using the Evaluate Formula tool, the result shown in the dialog box is the correct result of the formula up until I click on Evaluate for the last time, at which point, the result becomes #N/A. For example, one full formula is shown below: 
=IF(AND($S$96=TRUE,$U$96=TRUE),((HLOOKUP(G$98,Financials!$AE$5:$CQ$207,167,FALSE)*((HLOOKUP(G$98,Fin ancials!$AE$5:$CQ$207,148,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,173,FALSE)+HLOOKUP(G$98,Finan cials!$AE$5:$CQ$207,174,FALSE))/(HLOOKUP(G$98,Financials!$AE$5:$CQ$207,154,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,190,FALSE)+0 .0000001)))*'P1 IN'!$Y$69),IF(AND($S$96=TRUE,$U$96=FALSE), (HLOOKUP(G$98,Financials!$AE$5:$CQ$207,167,FALSE)*((HLOOKUP(G$98,Financials!$AE$5:$CQ$207,148,FALSE) +HLOOKUP(G$98,Financials!$AE$5:$CQ$207,173,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,174,FALSE))/(HLOOKUP(G$98,Financials!$AE$5:$CQ$207,154,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,190,FALSE)+0 .0000001))),0))
and the final result (all underlined) that I get before #N/A is: 
IF(TRUE,84.9095574195091,IF(AND($S$96=TRUE,$U$96=FALSE), (HLOOKUP(G$98,Financials!$AE$5:$CQ$207,167,FALSE)*((HLOOKUP(G$98,Financials!$AE$5:$CQ$207,148,FALSE) +HLOOKUP(G$98,Financials!$AE$5:$CQ$207,173,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,174,FALSE))/(HLOOKUP(G$98,Financials!$AE$5:$CQ$207,154,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,190,FALSE)+0 .0000001))),0)).
Since the TRUE argument is satisfied, I'm not sure why the formula returns #N/A rather than the number that is calculated.
Any and all help gratefully received.
Thanks,
Will.
I have built a fairly extensive financial model for evaluating drug trials. The model links three separate workbooks together.
I am now trying to replicate the model for a separate set of drug trials, which has necessitated saving the three workbooks under new names  but they all still link to one another (and when I update the links, this is confirmed).
Some of the formulae in the financial model return the #N/A value, but when I evaluate the formulae using the Evaluate Formula tool, the result shown in the dialog box is the correct result of the formula up until I click on Evaluate for the last time, at which point, the result becomes #N/A. For example, one full formula is shown below: 
=IF(AND($S$96=TRUE,$U$96=TRUE),((HLOOKUP(G$98,Financials!$AE$5:$CQ$207,167,FALSE)*((HLOOKUP(G$98,Fin ancials!$AE$5:$CQ$207,148,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,173,FALSE)+HLOOKUP(G$98,Finan cials!$AE$5:$CQ$207,174,FALSE))/(HLOOKUP(G$98,Financials!$AE$5:$CQ$207,154,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,190,FALSE)+0 .0000001)))*'P1 IN'!$Y$69),IF(AND($S$96=TRUE,$U$96=FALSE), (HLOOKUP(G$98,Financials!$AE$5:$CQ$207,167,FALSE)*((HLOOKUP(G$98,Financials!$AE$5:$CQ$207,148,FALSE) +HLOOKUP(G$98,Financials!$AE$5:$CQ$207,173,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,174,FALSE))/(HLOOKUP(G$98,Financials!$AE$5:$CQ$207,154,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,190,FALSE)+0 .0000001))),0))
and the final result (all underlined) that I get before #N/A is: 
IF(TRUE,84.9095574195091,IF(AND($S$96=TRUE,$U$96=FALSE), (HLOOKUP(G$98,Financials!$AE$5:$CQ$207,167,FALSE)*((HLOOKUP(G$98,Financials!$AE$5:$CQ$207,148,FALSE) +HLOOKUP(G$98,Financials!$AE$5:$CQ$207,173,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,174,FALSE))/(HLOOKUP(G$98,Financials!$AE$5:$CQ$207,154,FALSE)+HLOOKUP(G$98,Financials!$AE$5:$CQ$207,190,FALSE)+0 .0000001))),0)).
Since the TRUE argument is satisfied, I'm not sure why the formula returns #N/A rather than the number that is calculated.
Any and all help gratefully received.
Thanks,
Will.
Hi all,
I seem to be having trouble with using the HLOOKUP function and i cant understand what im doing wrong.
I simply want to look up max value (number) in a table row and return the header of that column (a text string).
My formula is =HLOOKUP(MAX(A2:H2),A1:H1,1,FALSE)
It just returns #N/A
Am i being really stupid, i have even just tried the HLOOKUP with out the MAX function in it... =HLOOKUP(B2,A1:H1,1,FALSE)
It still returns the #N/A
If i change the formula so that im looking for the Column title and asking it to return a value it works, is it something to do with searching for a number and returning a string?
Thanks
Ross
I seem to be having trouble with using the HLOOKUP function and i cant understand what im doing wrong.
I simply want to look up max value (number) in a table row and return the header of that column (a text string).
My formula is =HLOOKUP(MAX(A2:H2),A1:H1,1,FALSE)
It just returns #N/A
Am i being really stupid, i have even just tried the HLOOKUP with out the MAX function in it... =HLOOKUP(B2,A1:H1,1,FALSE)
It still returns the #N/A
If i change the formula so that im looking for the Column title and asking it to return a value it works, is it something to do with searching for a number and returning a string?
Thanks
Ross
Does anyone know how to increment the row index number in a hlookup formula? Without having to do it manually.
Thanks
Example Below.
=HLOOKUP(M2,B2:K12,11,FALSE)
=HLOOKUP(M2,B2:K12,10,FALSE)
=HLOOKUP(M2,B2:K12,9,FALSE)
=HLOOKUP(M2,B2:K12,8,FALSE)
Thanks
Example Below.
=HLOOKUP(M2,B2:K12,11,FALSE)
=HLOOKUP(M2,B2:K12,10,FALSE)
=HLOOKUP(M2,B2:K12,9,FALSE)
=HLOOKUP(M2,B2:K12,8,FALSE)
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
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
Hello Everyone,
I am wondering if anyone can help me understand what I have done with a spreadsheet I have been trying to modify.
Some time ago, I put in a formula to look up a value on another sheet and find a corresponding value to enter in the cell using HLOOKUP.
At the time, I had 2 columns for each date, now I need five columns for each date and I tried to copy over the formula but it doesn't work properly. It seems to work on the original columns (the first and fifth for the first date) but not in the other columns and I can't figure out why.
This is the formula
=IF(ISERROR(IF($A4=HLOOKUP($B$2,DCR!$G$11:$GP$14,2,FALSE),HLOOKUP($B$2,DCR!$G$11:$GP$14,4,FALSE),(IF($A4=HLOOKUP($B$2,DC R!$G$11:$GP$14,2,TRUE),HLOOKUP($B$2,DCR!$G$11:$GP$14,4,TRUE),0)))),0,(IF($A4=HLOOKUP($B$2,DCR!$G$11:$GP$14,2,FALSE),HLOO KUP($B$2,DCR!$G$11:$GP$14,4,FALSE),(IF($A4=HLOOKUP($B$2,DCR!$G$11:$GP$14,2,TRUE),HLOOKUP($B$2,DCR!$G$11:$GP$14,4,TRUE),0 )))))
This is what the sheet with the formula looks like:
01/05/11 A Dc Person1 5 0 0 0 Person2 1 0 0 0 Person3 0 0 0 0 Person4 0 0 0 0 Total 6 0 0 0
The formula is in the green cell.
This is what the sheet DCR looks like
Date 01/05/11 01/05/11 01/05/11 01/05/11 01/05/11 02/05/11 02/05/11 Person Person2 Person1 Person3 Person4 Person1 Person1 Person2 Book Activity 1 2 3 4 5 1 2
I am using Excel2003 for this.
I would be most grateful for any advice on what I might try to fix this problem.
Thanks
Ann Eliza
I am wondering if anyone can help me understand what I have done with a spreadsheet I have been trying to modify.
Some time ago, I put in a formula to look up a value on another sheet and find a corresponding value to enter in the cell using HLOOKUP.
At the time, I had 2 columns for each date, now I need five columns for each date and I tried to copy over the formula but it doesn't work properly. It seems to work on the original columns (the first and fifth for the first date) but not in the other columns and I can't figure out why.
This is the formula
=IF(ISERROR(IF($A4=HLOOKUP($B$2,DCR!$G$11:$GP$14,2,FALSE),HLOOKUP($B$2,DCR!$G$11:$GP$14,4,FALSE),(IF($A4=HLOOKUP($B$2,DC R!$G$11:$GP$14,2,TRUE),HLOOKUP($B$2,DCR!$G$11:$GP$14,4,TRUE),0)))),0,(IF($A4=HLOOKUP($B$2,DCR!$G$11:$GP$14,2,FALSE),HLOO KUP($B$2,DCR!$G$11:$GP$14,4,FALSE),(IF($A4=HLOOKUP($B$2,DCR!$G$11:$GP$14,2,TRUE),HLOOKUP($B$2,DCR!$G$11:$GP$14,4,TRUE),0 )))))
This is what the sheet with the formula looks like:
01/05/11 A Dc Person1 5 0 0 0 Person2 1 0 0 0 Person3 0 0 0 0 Person4 0 0 0 0 Total 6 0 0 0
The formula is in the green cell.
This is what the sheet DCR looks like
Date 01/05/11 01/05/11 01/05/11 01/05/11 01/05/11 02/05/11 02/05/11 Person Person2 Person1 Person3 Person4 Person1 Person1 Person2 Book Activity 1 2 3 4 5 1 2
I am using Excel2003 for this.
I would be most grateful for any advice on what I might try to fix this problem.
Thanks
Ann Eliza
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.
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.
Hi everyone  I've seen a few posts on various forums with questions that initially seem similar to mine, but the given answer doesn't help me (e.g., here). I'm using XL2007 with Windows XP. Here's my issue:
I have a table of values with a header row (the header row is a series of time points  0, 4.5, 9, 13.5, 18, etc.). Also, I have two cells that I am referencing for start and stop time points (so, let's say I want to use 4.5 and 13.5). Ideally, my formula would be able to use a time point for an HLOOKUP in the table and then drop down to the appropriate row to mark that cell as the part of the AVERAGE function range. My first attempt looks something like this:
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,2,FALSE):HLOOKUP($B$3,$D$5:$M$17,2,FALSE))
Excel doesn't like the above formula and my guess is because the HLOOKUP gives me values not cell addresses. Is there a function I can insert to get the cell address from the HLOOKUP? I tried to incorporate the ADDRESS and MATCH functions unsuccessfully. But I'm not even sure if those are what I need.
In the end, I want to be able to change the time points in the referenced cells (for this example, B2 and B3) and have my table of calculated values adjust their averages based on the new window of time points given.
Define my average window:
B2=4.5
B3=9
My table of averages:
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,2,FALSE):HLOOKUP($B$3,$D$5:$M$17,2,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,3,FALSE):HLOOKUP($B$3,$D$5:$M$17,3,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,4,FALSE):HLOOKUP($B$3,$D$5:$M$17,4,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,5,FALSE):HLOOKUP($B$3,$D$5:$M$17,5,FALSE))
etc...
Thanks for the help! Please let me know if something isn't clear.
I have a table of values with a header row (the header row is a series of time points  0, 4.5, 9, 13.5, 18, etc.). Also, I have two cells that I am referencing for start and stop time points (so, let's say I want to use 4.5 and 13.5). Ideally, my formula would be able to use a time point for an HLOOKUP in the table and then drop down to the appropriate row to mark that cell as the part of the AVERAGE function range. My first attempt looks something like this:
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,2,FALSE):HLOOKUP($B$3,$D$5:$M$17,2,FALSE))
Excel doesn't like the above formula and my guess is because the HLOOKUP gives me values not cell addresses. Is there a function I can insert to get the cell address from the HLOOKUP? I tried to incorporate the ADDRESS and MATCH functions unsuccessfully. But I'm not even sure if those are what I need.
In the end, I want to be able to change the time points in the referenced cells (for this example, B2 and B3) and have my table of calculated values adjust their averages based on the new window of time points given.
Define my average window:
B2=4.5
B3=9
My table of averages:
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,2,FALSE):HLOOKUP($B$3,$D$5:$M$17,2,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,3,FALSE):HLOOKUP($B$3,$D$5:$M$17,3,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,4,FALSE):HLOOKUP($B$3,$D$5:$M$17,4,FALSE))
=AVERAGE((HLOOKUP($B$2,$D$5:$M$17,5,FALSE):HLOOKUP($B$3,$D$5:$M$17,5,FALSE))
etc...
Thanks for the help! Please let me know if something isn't clear.
Hi
I need to use a Hlookup to look up dates, but I want to be able to type the text JAN, FEB, MAR etc into the cell that drives the HLOOKUP and need the formula to convert the text JAN into a date format in order to detect the columns?
Does that make sense?
I think it's something like
Code:
Something like that?
Many thanks
John
I need to use a Hlookup to look up dates, but I want to be able to type the text JAN, FEB, MAR etc into the cell that drives the HLOOKUP and need the formula to convert the text JAN into a date format in order to detect the columns?
Does that make sense?
I think it's something like
Code:
=hlookup(A1(mmm), c11:f25, 2, false))
Something like that?
Many thanks
John
I am having difficulty with the code below:
Code:
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.
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.
I need some help, this is my first time on this forum.
I got as far as returning the value I wanted if there is data in my spreadsheet with my HLOOKUP formula, however I want to make it dynamic so that when I add a row of data I don't have to keep changing the formulas. Here is the formula I am using =IF(HLOOKUP($DB$6,$CM$1:$CZ$73,9,FALSE)>0,1,HLOOKUP($DB$6,$CM$1:$CZ$73,9,FALSE)).
Thanks if anyone can help with this one it would be great.
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!
=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!
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
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
Hi all, having a problem combining an HLookup function with a sum.
I have a multi tab worksheet, and I'm trying to sum 3 cells based on an HLookup on page A, and the data is on page B.
Right now I have this formula, and it's giving me a #REF error.
=HLOOKUP($AA$18,'REV & COS'!C67:O83,SUM('REV & COS'!E76:E78),FALSE)
Any help?
Thanks all.
I have a multi tab worksheet, and I'm trying to sum 3 cells based on an HLookup on page A, and the data is on page B.
Right now I have this formula, and it's giving me a #REF error.
=HLOOKUP($AA$18,'REV & COS'!C67:O83,SUM('REV & COS'!E76:E78),FALSE)
Any help?
Thanks all.
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
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
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'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 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
Hello guys,
I'm having a problem with a formula I have to repeat a lot of times that, if I can make automatic, can save me literally a lot of time.
The formula is the following:
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD2;FALSE);"#N/A N/A")
in cell B2, and I drag it down until B17 and then to the right until AB17. In cell 1 there is what I need to look in the HLOOKUP formula, and are fixed values.
In the AD column I have this formula:
=MOD(ROW();18)
and I want this formula to be copied down in cell B20 with this result
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD2;FALSE);"#N/A N/A")
to be dragged down and to the right again in order to cover that area, and then down again in cell B38 to be like
=IFERROR(HLOOKUP(B$1;Prep!$B$37:$AB$53;$AD2;FALSE);"#N/A N/A")
An example of how the formula appear in the B column is:
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD2;FALSE);"#N/A N/A") > starting in B2, in B1 I have years, 1986 to 2012
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD3;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD4;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD5;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD6;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD7;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD8;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD9;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD10;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD11;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD12;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD13;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD14;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD15;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD16;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$1:$AB$17;$AD17;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD2;FALSE);"#N/A N/A") > starting in B20, in B19 I have years, 1986 to 2012
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD3;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD4;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD5;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD6;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD7;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD8;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD9;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD10;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD11;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD12;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD13;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD14;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD15;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD16;FALSE);"#N/A N/A")
=IFERROR(HLOOKUP(B$1;Prep!$B$19:$AB$35;$AD17;FALSE);"#N/A N/A")
I will then drag them to the right to fill the space until the AB column.
I have to use this formula until cell B53984 that's why I'm asking if there is any way to make it automatic.
Let me explain why I'm trying to do this: in the Prep worksheet I have 3000 companies and I'm analyzing a time series data for 16 balance sheet items. I'm analyzing this starting from 1986 to 2012. The problem is that I'm downloading this with Bloomberg, and on the B column data starts from the first year available for that company, and it might also skip years where there is no data available for that specific company. So in the B column a company might have data from 1997, another from 1989, another from 2001, and it might happen that the first does not have a 1998 so in the C column I have 1999.
What I'm doing is organizing data in order to have each year in the same column: I'm therefore looking for the year in the Prep sheet (raw 1 in the first case) and if it's the same as the one in my actual sheet it then pastes the value of that row in the new table; if there is not it will put #N/A N/A. So all companies will have the same years in the same column.
The problem is that I have to do this 3000 times (then copy this excel worksheet into others and adjust formulas, since I have other 24 sheets where I have to do this). I managed to make the row selection automatic using the =mod(row();18) formula, but I cannot make the table selection shift down automatically.
Is there any way to make it automatic, maybe using macros (which I've never used before)? I just cannot adjust it manually for so many times.
I don't know if I can upload the file since it's around 60MB.
If you need more infos to understand my problem and help me, please feel free to ask.
Thank you again for your help,
Manuel
I have Drawing revisions A, B, C on first row and its release date in second row.
I wanted to print the latest revision no. by comparing the dates. I tried
using hlookup :
=HLOOKUP(MAX(B5:D5),B4:D5,1,FALSE)
but it is not working, showing error # N/A!
I read in help that HLOOKUP will search in first row and display value of second row for that corresponding value.
I tried INDEX MATCH function also. but it is not working.
Can someone help me out in this.
I wanted to print the latest revision no. by comparing the dates. I tried
using hlookup :
=HLOOKUP(MAX(B5:D5),B4:D5,1,FALSE)
but it is not working, showing error # N/A!
I read in help that HLOOKUP will search in first row and display value of second row for that corresponding value.
I tried INDEX MATCH function also. but it is not working.
Can someone help me out in this.
I'm trying to insert an INDIRECT into a HLOOKUP to instruct the HLOOKUP on which row index to use. I have got as far as the below but am getting #REF! error.
=HLOOKUP(Sheet1!B1,Sheet2'!A1:BE21920,INDIRECT(A2),FALSE)
Can anyone help?
=HLOOKUP(Sheet1!B1,Sheet2'!A1:BE21920,INDIRECT(A2),FALSE)
Can anyone help?
Need a HLOOKUP that will only returned the value if it is a postive number otherwise I need it to be blank or zero.
=HLOOKUP($E$1,$S$2:$AP$16,6,FALSE)
=HLOOKUP($E$1,$S$2:$AP$16,6,FALSE)
I am trying to combine OFFSET and HLOOKUP but have some probs.
I have a HLOOKUP that returns the correct value..........however instead of pulling that value... I would like to pull the value to the right 1 column.
Example:
=HLOOKUP($A$7,'JAN IND PROD'!$A$2:$Y$70,34,FALSE)
But on the JAN IND PROD file I want to pull the column to the right of the 34 columns below the referenced A7. Make sense?
I was thinking...
=OFFSET(HLOOKUP($A$7,'JAN IND PROD'!$A$2:$Y$70,34,FALSE)1,2,1,1)
But that does not work.....
THANKS!
I have a HLOOKUP that returns the correct value..........however instead of pulling that value... I would like to pull the value to the right 1 column.
Example:
=HLOOKUP($A$7,'JAN IND PROD'!$A$2:$Y$70,34,FALSE)
But on the JAN IND PROD file I want to pull the column to the right of the 34 columns below the referenced A7. Make sense?
I was thinking...
=OFFSET(HLOOKUP($A$7,'JAN IND PROD'!$A$2:$Y$70,34,FALSE)1,2,1,1)
But that does not work.....
THANKS!
I am trying to figure out a way to clean up the formula below. It calculates a weighted average over a 4 week period. I recently inherited the upkeep of this spreadsheet and was asked to include a 5th week to this weighted average.
E1 is the date of the most recent Monday. E333, E334, and E335 are the dates of the next 3 Mondays to reference. These can be the previous Mondays but can also be overridden, hence, the cell reference.
I'm not really keen on adding another argument into this formula and figure there has to be an easier way to calculate this. I also want to make it easier to add or remove weeks.
You have been a great reference for me on many issues and normally I can find a solution somewhere in the site. I couldn't this time as I guess I couldn't figure out the right keywords to search for.
=IF(SUM(HLOOKUP(E$1,Mon!$C$1:$DC$707,$C29,0)+HLOOKUP(E$333,Mon!$C$1:$DC$707,$C29,0)+HLOOKUP(E$334,Mon!$C$1:$DC$707,$C29, 0)+HLOOKUP(E$335,Mon!$C$1:$DC$707,$C29,0))=0,0,SUM(HLOOKUP(E$1,Mon!$C$1:$DC$707,$B29,0)+HLOOKUP(E$333,Mon!$C$1:$DC$707,$ B29,0)+HLOOKUP(E$334,Mon!$C$1:$DC$707,$B29,0)+HLOOKUP(E$335,Mon!$C$1:$DC$707,$B29,0))/SUM(HLOOKUP(E$1,Mon!$C$1:$DC$707,$C29,0)+HLOOKUP(E$333,Mon!$C$1:$DC$707,$C29,0)+HLOOKUP(E$334,Mon!$C$1:$DC$707,$C29,0)+H LOOKUP(E$335,Mon!$C$1:$DC$707,$C29,0)))
Thank you for any help! I just want to make this easier to read and adjust as needed.
E1 is the date of the most recent Monday. E333, E334, and E335 are the dates of the next 3 Mondays to reference. These can be the previous Mondays but can also be overridden, hence, the cell reference.
I'm not really keen on adding another argument into this formula and figure there has to be an easier way to calculate this. I also want to make it easier to add or remove weeks.
You have been a great reference for me on many issues and normally I can find a solution somewhere in the site. I couldn't this time as I guess I couldn't figure out the right keywords to search for.
=IF(SUM(HLOOKUP(E$1,Mon!$C$1:$DC$707,$C29,0)+HLOOKUP(E$333,Mon!$C$1:$DC$707,$C29,0)+HLOOKUP(E$334,Mon!$C$1:$DC$707,$C29, 0)+HLOOKUP(E$335,Mon!$C$1:$DC$707,$C29,0))=0,0,SUM(HLOOKUP(E$1,Mon!$C$1:$DC$707,$B29,0)+HLOOKUP(E$333,Mon!$C$1:$DC$707,$ B29,0)+HLOOKUP(E$334,Mon!$C$1:$DC$707,$B29,0)+HLOOKUP(E$335,Mon!$C$1:$DC$707,$B29,0))/SUM(HLOOKUP(E$1,Mon!$C$1:$DC$707,$C29,0)+HLOOKUP(E$333,Mon!$C$1:$DC$707,$C29,0)+HLOOKUP(E$334,Mon!$C$1:$DC$707,$C29,0)+H LOOKUP(E$335,Mon!$C$1:$DC$707,$C29,0)))
Thank you for any help! I just want to make this easier to read and adjust as needed.
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
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 Guys,
I have been given a spreadsheet to fix as the user says the charts/graphs aren't adding the new data.
But when I looked closer at the workbook the whole thing just seemed a little wrong to me
Please see the workbook attached.
All the data is entered into the sheet 'Data Entry'.
Then on the 'Data' sheet it should pull thru some key values but half of it isn't done and it isn't very smart.
I have created a 'TEST Data' sheet so I could start messing with it.
I was going to pull the data thru from 'Data Entry' into the 'TEST Data' sheet using a Hlookup to make sure it worked before i messd with the actual 'Data' sheet.
The problem I am having is moving/copying the Hlookup across.
On the 'TEST data' sheet the Hlookup is as follows and is correct:
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,2,FALSE)
But the next set of (Week 2) is not in the 2nd or even 3rd row of the array, its in row 11......... thats 9 rows down from the original.
Moving on the data i want to bring back is always 9 more than the last so the Hlookups should look like:
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472, 11 ,FALSE)
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472, 20 ,FALSE)
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472, 29 ,FALSE)
etc etc
Can any of you guys out there help on how I can do this or it may not even be a Hlookup but something even smarter!!
Thanks in advance
I have been given a spreadsheet to fix as the user says the charts/graphs aren't adding the new data.
But when I looked closer at the workbook the whole thing just seemed a little wrong to me
Please see the workbook attached.
All the data is entered into the sheet 'Data Entry'.
Then on the 'Data' sheet it should pull thru some key values but half of it isn't done and it isn't very smart.
I have created a 'TEST Data' sheet so I could start messing with it.
I was going to pull the data thru from 'Data Entry' into the 'TEST Data' sheet using a Hlookup to make sure it worked before i messd with the actual 'Data' sheet.
The problem I am having is moving/copying the Hlookup across.
On the 'TEST data' sheet the Hlookup is as follows and is correct:
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,2,FALSE)
But the next set of (Week 2) is not in the 2nd or even 3rd row of the array, its in row 11......... thats 9 rows down from the original.
Moving on the data i want to bring back is always 9 more than the last so the Hlookups should look like:
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472, 11 ,FALSE)
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472, 20 ,FALSE)
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472, 29 ,FALSE)
etc etc
Can any of you guys out there help on how I can do this or it may not even be a Hlookup but something even smarter!!
Thanks in advance
Hi All,
I am new to Mr Excel so I hope I can find some help out there.
I am in the middle of creating an statistical worksheet for a sales floor (pretty standard?), however, I want the sheet to do most of the work so I have created the spreadsheet as follows;
I have 1 sheet which I load all the statistical information into and another few to show results in english  for example, moth to date, week to date.
One of my ideas is to have just one sheet where you can ask for the information you need.
I understand a simple HLOOKUP function (I think) but is it possible to say to a cell; calculate a sum between cell A1 (which is a date) row 10 (using HLOOKUP) and B1 (also a date) row 10 (using HLOOKUP).....?
Hope this makes sense  I downloaded the HTML Maker Utility but (call me stupid) I don't understand what to do with it.
Thanks for any help
I am new to Mr Excel so I hope I can find some help out there.
I am in the middle of creating an statistical worksheet for a sales floor (pretty standard?), however, I want the sheet to do most of the work so I have created the spreadsheet as follows;
I have 1 sheet which I load all the statistical information into and another few to show results in english  for example, moth to date, week to date.
One of my ideas is to have just one sheet where you can ask for the information you need.
I understand a simple HLOOKUP function (I think) but is it possible to say to a cell; calculate a sum between cell A1 (which is a date) row 10 (using HLOOKUP) and B1 (also a date) row 10 (using HLOOKUP).....?
Hope this makes sense  I downloaded the HTML Maker Utility but (call me stupid) I don't understand what to do with it.
Thanks for any help