Hlookup In Different Workbooks

Jan 31, 2007

I used the following formula when the worksheet was in the same workbook.

=HLOOKUP($E$6,INDIRECT("'"&B14&"'!$A$1:$N$2"),2,)

The information in cell B14 is the same as the target sheet name.

I want to move the target sheet to another workbook.

How would i need to adjust the formula in order to return the same information from this new workbook?

View 3 Replies


ADVERTISEMENT

HLOOKUP; HLookup To Find An Adjusted Midterm Grade

Oct 30, 2007

i'm trying to use HLookup to find an adjusted midterm grade that's given. but i have some conditions:

If student missed exam and has a zero – keep zero.
If student has a grade of 1-119 points, increase their grade 40 points.
If student has a grade of 120-125 points, increase their grade 35 points.
If student has a grade of 126-131 points, increase their grade 31 points.
If student has a grade of 132-139 points, increase their grade 27 points.

with these conditions, if my midterms grade is 120, how would i calculate it using HLookup? i worked on it but i keep getting the #NA! error.
=H4+HLOOKUP(H4,B24:D25,2).

View 5 Replies View Related

HLOOKUP In HLOOKUP, Base Estimate Table In Excel

Jul 10, 2009

I am trying to import a BASE ESTIMATE table into EXCEL.

I have problems with most of the formulas, especially this one:

=VLOOKUP($E$2,$B$24:$P$604,HLOOKUP($E$3,$D$22:$L$604,1)+2)*HLOOKUP(HLOOKUP($E$3,$D$22:$L$604,1),$D$2 2:$L$23,2)

and this one

=ROUND((IF(AND(OR(E7>E5,E7>E6),E3<40000),E7,IF(AND(E6<E5,E3<40000),E6,E5)))*E8*1.055*1.06,2)+10

I am not sure if EXCEL allows a HLOOKUP within an HLOOKUP. If not, how can I get around this?

View 14 Replies View Related

Hlookup Only Returns First Value

Dec 27, 2013

I am facing a problem using Hlookup function. I am using Hlookup to do a control check of consolidation i am doing here, however it only returns the first found value instead of all correct values (or range).

I am attaching a file with an example. In the file formula is used in Supply(S) sheet on row 73.

Attached File : HlookupExample.xlsx‎

View 3 Replies View Related

HLookup V If Statements

Feb 7, 2014

I have various 6 x 6 blocks which contain just numbers, in another part of the spread sheet I have a 1 x 6 block of numbers. What I need to do is to check whether any of the numbers in the 1 x 6 block appear in any of the 6 x 6 blocks.If that does occur then I want to make a specific cell increase by the times that the match happens.using HLookup but just got errors and the If Statement, although did work, went on and on and on.

View 6 Replies View Related

Hlookup Not Incrementing..

Jan 10, 2010

Im using the following HLOOKUP formula : =HLOOKUP($B$14,$DA$15:$EH$380,2,FALSE) and I need to drag it down 365 times, but the row index num, ie ‘2’ does not increment with the drag. Is this correct – or is there a way of making the 2 increment?

View 3 Replies View Related

Vlookup/Hlookup

Oct 23, 2007

I have some experience with programming, so I understand the theory behing things like loops, arrays and functions etc...Only problem is, I need to make a program at work to ease workloads and I'm afraid I have no idea where to begin.

Basically, I need something similar to a lookup, but thanks to the limit of IF statements available in the formula bar, I can't write what I need.

The program I need to write wouldbe similar to an autofill function.
On Sheet1 of my spreadsheet, there are fields:
Name, Address 1, Address 2, Address 3, Postcode, Area, Rep....
(The last three are the most important)
On sheet two, Postcode, Area, Rep already have data entered in thier respective columns. (D3, E3, F3).

What I need done on Sheet1 is when the user enters the first two characters of a postcode, the 'program' will lookup the entire row respective to the postcode that is entered. when enter is pressed, the Postcode will copy itself from the other sheet as well as the Area number and the Rep into the empty fields in Sheet1. The other way to do this is look through a long list until the right postcode for an Area is found then assign the Rep the Contact details (Add. 1, Add. 2, Add. 3...).

View 11 Replies View Related

How To Use Offset With HLookup

Jan 30, 2014

I want to do an HLOOKUP but I need to skip row in the formular. See details below:

Sheet1:
.
A
B
C
D
E
F
G

[Code]...

(how do i change the font in red with formular) SUMMARY SHEET
.
A
B
C
D
E
F
G

[Code]...

View 3 Replies View Related

Sum Up HLookUp Values

Feb 27, 2014

I have a huge data and I need to find values through HLookup and than have to find the sum of that whole column.But when I tried to to so It is showing error as Formula broken.

sum(HLOOKUP((HLOOKUP(F7,B1:D5,2,TRUE)),B1:D5,3,1):HLOOKUP((HLOOKUP(F7,B1:D5,2,TRUE)),B1:D5,4,1))

Here f7 = the value which need to be find.

It is like

Abc
Cdb
1
2
3

and i want the sum of (1,2,3) and have given to search for Abc

View 1 Replies View Related

Hlookup + Vlookup Togethor

Jan 8, 2009

I have the following spreadsheet lets say.

I would like to match the name and the date and get the relavent figure from the "range" name

how can i do this?

View 2 Replies View Related

Hlookup (retrieve The Corresponding Data)

Sep 7, 2009

The formula I'm looking for should look for the name mentioned in B2 in range AC2:AF400 and retrieve the corresponding data below it starting from D11 to D41 and continue on F7 to F34 etc.

View 3 Replies View Related

Function Of HLOOKUP With Missing Value

Sep 21, 2009

I have a formula that looks like this:
=HLOOKUP('Output'!$B$3,'Input'!$B$1:$P$300,'Input'!$A3+1,FALSE)

It is working very fine, when the cell has a value, but when it doesn't - it will return 0. So my question is: is there any way to make it return certain value or word like "No value" instead of zero?

View 9 Replies View Related

Combine VLookup And HLookup

Jan 10, 2012

I have an Excel workbook containing a number of worksheets. My problem is that on one sheet(BlkPick) I have Rows containing headings(Location Names)and a column containing Product names. I wish to lookup the corresponding value contained at the matching intersection on another worksheet. I have searched this forum along with google and found that INDEX & MATCH are what I should be combining with Vlookup and these work when used on the same worksheet but when I test using say MATCH alone I get an error when attempting to MATCH my result from a different worksheet.

View 9 Replies View Related

Summing HLookup Values

Jul 20, 2006

I have a chart of numbers pertaining to progress of a project by month. The
end of month dates are in a row across the top, the progress % are in
corresponding rows and columns below:

31-mar-06 30-apr-06 31-may-06 30-jun-06
5.5 3.4 1.2 2.6

I'm trying to do a SUM of HLOOKUP values to get a total progress for a given
month. For example, I need to see the total progress for end of May from end
of March: SUM(HLOOKUP(5.5) to HLOOKUP(1.2))=5.5+3.4+1.2 in a cell at the
end of the row. How can I do this? Do I need to use another function?

View 14 Replies View Related

Can HLOOKUP Return Words

May 13, 2007

Really basic question - I'm trying to get HLOOKUP to lookup a number and return text that is in another cell but it keeps returning #N/A. I know the formula works because when I change the text (that I want returned) into a number it returns the figure. How can I get round this?

View 10 Replies View Related

Hlookup Stonedead In 2002

Apr 5, 2008

Is the Hlookup funktion not included in Excel 2002? I've been trying to run basic exemples from microsoft, and ones made by myself, and I just can't get it to work. Excel just keeps telling me that's it wrong in the formula. I really need hlookup work.

View 13 Replies View Related

Combining Index And Hlookup?

Apr 20, 2009

I have created and named a table with salaries based on years of experience and education.

I am having trouble writing a function to search for a specific amount and result in the the cell below that specific amount.

So people are moving up by one year on the schedule I would like to use their current salary to lookup and display their salary for next year.

I am confused on how to incorporate the different functions I am assuming an index and a hlookup...

View 11 Replies View Related

Non Exact Matches In HLOOKUP

Oct 27, 2009

Is it possible to use HLOOKUP without exact matches. e.g. < =HLOOKUP(E1,Actuals,2) > where E1 is "2009 October" and the lookup value in named range "Actuals" would be simply "Oct".

View 4 Replies View Related

HLookup Plus Index Match?

Jan 16, 2012

GreenBlueWhiteCarBoatSailA235447424231B465436742354C565434763261

using the table above i am trying to do a three match to return a value. I am using the following formula and keep getting a #value error. essentially the formula is returning "Green"*2354. What I am wanting to return is the value 2354.

=HLOOKUP(G4,F4:I8,1,FALSE)*(INDEX(Table,MATCH($F6,INDEX(Table,,1),0),MATCH(G$5,INDEX(Table,2,),0)))

View 7 Replies View Related

HLookup On Alternate Cell?

Mar 5, 2012

Im wondering if their is a formula to return the results in a Hlookup but adjusted for the cell 1 cell to the right?

For instance if Row 1 Column one contains "Tom Jones" that the forumula knows to return the value in row 2 column 2 instead of row 2 column 1.

View 1 Replies View Related

Average Percentage On HLookup

Apr 24, 2012

I want to get an Average % on an HLookup. This value should come from a range with weekdays. What i need is an average of all values on monday.

=AVERAGE(HLOOKUP($H$18;Billrun!$13:$18;2;FALSE)

This i what i have, and it keeps returning 0%. I have a feeling it includes all empty values...

View 5 Replies View Related

HLookup For Words Not Figures

Jul 15, 2012

I am using a Hlookup to retrieve Words and Figures but when retrieve words it is failed and output is figures ONLY while I need the same word to be shown in the cell value?

View 2 Replies View Related

Use ADDRESS Function In HLOOKUP?

Aug 12, 2012

I am getting #VALUE! when I use the formula to dynamically load some values to the column

simplified formula will look like below.

=IF(HLOOKUP("C",ADDRESS(ROW()-1,COLUMN(),4 ),1,FALSE)="C","C","A")

check the row above this/current column exact match if the value in that row is "C" then put "C" in this/current row and column else put "A".

example:
C
C
C
? (should be C)

A
A
A
? (should be A)

B
B
B
? (should be A)

View 6 Replies View Related

Combining HLOOKUP With SUMPRODUCT

Sep 3, 2013

I am currently using this formula to total the items in Column AH of my worksheet, which usually begins on row 19 (Occasionally on row 20, which corrupts my formula). Since the numbers are always below the header, entitled "Exception Amount" I am wanting to know if I can combine these to formulas into one that will correct the occasional anomaly.

=SUMPRODUCT(($AH$20:$AH$300>0)*$AH$20:$AH$300)

View 2 Replies View Related

How To Use Multiple Sheets With HLookup

Apr 19, 2014

hlookups. I have written a formula "T(HLOOKUP($H$2,Jan-Apr!$F$5:$EO$10,3,0))" where H2 is the date that I want to match on the other sheets. It works for the sheet Jan-Apr but I have 2 other sheets May-Aug & Sept-Dec. Unfortunately Im at a loss when it comes to writing the formula to include the 2 other sheets?

View 6 Replies View Related

Using Hlookup With Named Ranges?

May 28, 2014

I have 8 branch's data (on 8 separate sheets) which I have Named (in excel) by referring to the range for that Branch's data. My named ranges are:

ct_lookup_act
dbn_lookup_act
pe_lookup_act

etc.

On the first sheet of the workbook I would like to be able to select a branch from a dropdown and then the sheet should display selected data only for that branch using a Hlookup function (all the data for each branch are in identical sized ranges and are ordered the same).

I have tried the following:

Cell A1 = dropdown list of available branches (ct,dbn,pe....etc) - as an example choose "ct"
Cell A2 = concatenate (a1,"_lookup_act") - and I have named cell A2 as "which_branch" - and in this example the data in A2 would be "ct_lookup_act"

Cell A5 = hlookup("Mar 14",which_branch,2,false)

Result:

the hlookup function returns #N/A because it is looking for "Mar 14" in the range defined by "which_branch" - ie cell A2, instead of looking in the range defined by "ct_lookup_act" (as an example).

View 3 Replies View Related

Hlookup Over Multiple Rows?

Jul 31, 2014

I have the following table of data:

Jun Gross Profit  YZAAABACADAEAF45MonTuesWedThursFriSatSunTotal
46      1 47      6,166       6,166 48        492345678 505,2187,1159,4868,06413,75512,8076,166     62,611 51        529101112131415 535,2187,1159,4868,06413,75512,8076,166     62,611 54        5516171819202122 565,2187,1159,4868,06413,75512,8076,166     62,611 57        5823242526272829 595,2187,1159,4868,06413,7559,9617,589     61,188 60        6130       626,641             6,641

What I would like is to be able to type in a number (1-30) and have it give me the number underneath that number. So if I typed in the number 18, it would give me 9,486.

I have tried index(match), dynamic ranges, but I must be doing something wrong.

I know organizing the data differently would be easier, but I have external software that spits out the reports like this so continuously reorganizing the data would end up taking a lot more time.

View 4 Replies View Related

Hlookup To Return Address Instead Of Value

Oct 23, 2008

I have a workbook full of worksheets like so:

Main
Second
Third
Fourth

The main worksheet is a roll up page of sorts. It contains all of the information on all three other tabs. All updates entered on the main sheet will be distributed accordingly to each sheet the needs it VIA hlookup. At times, certain updates will be highlighted certain colors. From what I understand, there is no way to copy the color formatting using formulas and return the value.

So, I am trying to figure out a way to use VBA for this. I have dabbled a bit in VBA have think I have found it, except I have run into a substantial road block.

I will use the .hasformula to first check all cells in a range on the other sheets. If there is a formula, it will be an Hlookup only. Once it finds the formula, it then needs to copy the color of the source cell the hlookup is using to return data. It needs to copy the color of the cell, not the color of the text.

View 9 Replies View Related

HLOOKUP: Two Words Along A Range Along A Row

Oct 18, 2009

i need to look for two words along a range along a row. i need to know if only one of the words is found or if both are found. ie if apples or oranges or both are found in a range along one row.

View 9 Replies View Related

Hlookup Embeded Within Vlookup

Oct 21, 2009

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.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved