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)
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.
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
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!
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)))))
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 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
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 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
Hi all,
I have 2 dropdown boxes created from Data Validation and I want to create a formula to say IF(b2="product 1",hlookup(b4,range1,3,false),else if(b2="product 2",hlookup(b4,range2,3,false), etc etc but I have 10 product and it's getting quite long and tedious to write 10 if/ hlookup statements.
Note: b2 and b4 are the 2 dropdown boxes I want the formaul to look at and all the data ranges are held in another worksheet.
Is there a formula that is better than what I have stated above, and will also give me the results I require?
Thanks,
Aurora
I have 2 dropdown boxes created from Data Validation and I want to create a formula to say IF(b2="product 1",hlookup(b4,range1,3,false),else if(b2="product 2",hlookup(b4,range2,3,false), etc etc but I have 10 product and it's getting quite long and tedious to write 10 if/ hlookup statements.
Note: b2 and b4 are the 2 dropdown boxes I want the formaul to look at and all the data ranges are held in another worksheet.
Is there a formula that is better than what I have stated above, and will also give me the results I require?
Thanks,
Aurora
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))))
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))))
Is there any way to nest more than 7 IF functions on Excel 02?
I have a list which i want people to be able to select a tab title and have it bring up particular information from that tab using a hlookup. The problem is is that i have 9 tabs to choose from, and they can't be shrunk down.
=IF(A2="Wet Can",HLOOKUP(A3,'Wet Can'!C3:G29,9,0),IF(A2="Tray",HLOOKUP(A3,Tray!C3:G29,9,0),IF(A2="Pouch",HLOOKUP(A3,Pouch!C3:G29,9,0),IF(A2="Dry",HLOOKUP(A3,Dry!C3:G29,9,0),IF(A2="Jerky",HLOOKUP(A3,Jerky!C3:G29,9,0),IF(A2="Nutri",HLOOKUP(A3,Nutri!C3:G29,9,0),IF(A2="Bird",HLOOKUP(A3,Bird!C3:G29,9,0),"other")))))))
That is the extent of the formula that i can have, it is basically saying that IF the term in the drop down list=a particual tab name(of which there are 9) then find the data within that tab, else move to the next title.
How can i do this with 9 tabs?
I have a list which i want people to be able to select a tab title and have it bring up particular information from that tab using a hlookup. The problem is is that i have 9 tabs to choose from, and they can't be shrunk down.
=IF(A2="Wet Can",HLOOKUP(A3,'Wet Can'!C3:G29,9,0),IF(A2="Tray",HLOOKUP(A3,Tray!C3:G29,9,0),IF(A2="Pouch",HLOOKUP(A3,Pouch!C3:G29,9,0),IF(A2="Dry",HLOOKUP(A3,Dry!C3:G29,9,0),IF(A2="Jerky",HLOOKUP(A3,Jerky!C3:G29,9,0),IF(A2="Nutri",HLOOKUP(A3,Nutri!C3:G29,9,0),IF(A2="Bird",HLOOKUP(A3,Bird!C3:G29,9,0),"other")))))))
That is the extent of the formula that i can have, it is basically saying that IF the term in the drop down list=a particual tab name(of which there are 9) then find the data within that tab, else move to the next title.
How can i do this with 9 tabs?
I'm trying to setup a Hlookup formula so that it gets the sheetname from a value in cell B1 of the second sheet. I think I have to use indirect but cannot fathom it out.
This is my current formula =HLOOKUP(E6,Sheet1!A1:D750,14,FALSE)
This is my current formula =HLOOKUP(E6,Sheet1!A1:D750,14,FALSE)