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
ADVERTISEMENT
Jan 2, 2014
I need a formula that will combine hlookup and vlookup to return one value.
View 14 Replies
View Related
Apr 7, 2014
I have data like this in data have tab in my sample file
POSSIBLEREDYELLOWGREENPINKVIOLET
GRADE/GRADEA/CG/GA/GG/GA/A
GRADE/GRADEC/CG/GG/GG/GA/A
GRADE/GRADEA/CC/CA/AA/AC/C
GRADE/GRADEA/CG/GA/GA/GA/C
GRADE/GRADE-----
and i have this data in lookup tab in my sample file
possibleREDYELLOWGREENPINKVIOLET
COLORC/CG/GG/GG/GA/A
GRADEA/AC/CA/AA/AC/C
now i want to do like this
if B2 value (red column) of datahave tab is equal to B2 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to A.
if B2 value (red column) of datahave tab is equal to B3 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to B.
if B2 value (red column) of datahave tab is not equal either B2 and B3 value of red column in lookup tab then B2 value (red column) of datahave tab is equal to H.
[code]....
i tried normal Hlookup but i do not know how combine multiple IFs to solve my problem.
View 4 Replies
View Related
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
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
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
Jul 17, 2012
I am looking to pull out an outcome based on the user inputting two criteria, below is an example:
A
B
C
D
E
F
G
H
I
J
[code].....
So if the user inputted P1 into cell H2 and inputted 9 in I2, J2 would show Pass, but if they inputted P5 into cell H2 and inputted 9 in I2, J2 would show Fail.
View 5 Replies
View Related
Feb 14, 2010
I don't want VB code but a worksheet formula.
I have a table full of names and cars sold. The table tracks the person's progress. It is like a diary. It's a simplied version of the DB I am currently working with.
Is it possible to do the following things? I have basically 2 types of questions.
1)When was the last red car sold?
2)When was a car sold irrespective of color?
The answer to each question will be put in separate columns.
I have only included January but I will do this for the entire year of 2010 and also I will do this for all of the names in my database. I think there are about 300.
When was the last time ANDY sold a RED car?
The answer should be 1/12/2010
The answer will be put in ROW A COLUMN I
When was the last time ANDY sold a car irrespective of color?
The answer should be 1/12/2010
The answer will be put in ROW A COLUMN J
When was the last time BILL sold a RED car?
The answer should be 1/10/2010.
The answer will be put in ROW B COLUMN I
When was the last time BILL sold a car irrespective of color
The answer should be 1/11/2010
The answer will be put in ROW B COLUMN J
I have attached a thumbnail of a sample Database
View 9 Replies
View Related
Jun 30, 2006
I was wondering is it possible to perform a VLOOKUP AND HLOOKUP in the same formulae or is there another way to perform this task. the attached example
View 2 Replies
View Related
Oct 13, 2009
I would like to drop down the cells in B4 and D4 so that if throws up a result in D6 which looks at Sheet 2 and throws back that score? So it will look for Neil in Column A and October in Row 1 and throw back that figure.
View 3 Replies
View Related
Nov 23, 2012
I want to extract/generate data from a database (same as like we can do with filter or pivot method) with H/VLookup or any other method.
Like:
Sheet 1 (Database)- 23-column & 690-Rows having record of 9 Different Groups)
Sheet 2 (Report)- A2 contains List of 9 Different Groups, If I select Group 3 from that list then I want to show all relevant data of Group 3(23-Column) in this sheet.
View 9 Replies
View Related
Oct 13, 2011
I am doing a vlookup on a cell range where the value I'm trying to lookup (a date) exists, but it's not an actual number in the cell...it's a reference to another cell with that value (somewhere completely different).
So, I'm trying to vlookup(date(1/1/2011),A1:A12,2,false) to get the B column value.
A B
1/1/2011 #
2/1/2011 #
...
12/1/2011 #
However, the A column is not the actual date. It is a reference to another cell somewhere completely different that has the actual date 1/1/2011.
When I do a vlookup trying to find 1/1/2011, it can't see it there unless I overwrite the reference in A1 (for instance) with the actual date.
Can I do a vlookup and keep my cell references?
View 6 Replies
View Related
Oct 28, 2009
This was a snap in Lotus. Unfortunately, the VLOOKUP and HLOOKUP formulas don't translate to Excel on conversion. I need to estimate the weight of reinforcing steel in concrete based on 7 different sizes of rebar and spacing of the bar between 1" and 24".
I have created a matrix(lookup table) with the bar spacing listed in decimal equivilents in feet on the top row.Inthe row below, I have numbered the columns from 1 to 24. the next row is blank. Along the left side of the matrix, I have listed the bar sizes 3 thru 10. Within the table I have listed all the bar weights/SF of concrete. IE if the slab has 1 mat of #5 rebar spaced 6" oc. If you look in row 7, under column 6, the weight of weight of the bar in that sf of concrete is found. In the part of the worksheet where the caculations are performed set up as follows:.....
View 4 Replies
View Related
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
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
May 5, 2008
I need to lookup two different columns and if values match, then bring back one value:
For Example:
Data:CityNameAtlantaDaveNew YorkCharlieLos AngelesDanSt. LouisDanMemphisSteven
Lookup Table:CityNameCodeAtlantaDaveDAVNew YorkCharlieCHALos AngelesJavierJAVSt. LouisDanDANMemphisRobertROB
The formula I'm looking for would produce the following results:
If City on Data matches the city on Lookup Table, and the name on data matches the name on Lookup Table, then bring back the code on Lookup Table.
Result would be:
Results:CityNameResultsAtlantaDaveDAVNew YorkCharlieCHALos AngelesDanWill be blank, as the names on Data and LookUp Table don't matchSt. LouisDanDANMemphisStevenWill be blank, as the names on Data and LookUp Table don't matcm
View 9 Replies
View Related
Apr 19, 2007
Is it possible to combine Vlookup & Max?
In column A I have product codes some of which are repeated, in column B I have costs for the relevant product codes.
I want to vlookup the product code on another sheet, then Columns A & B picking up the cost in column B but if there are mutiple entries I want to pick up the highest cost out of all the entries with that product code.
View 5 Replies
View Related
Jan 21, 2014
I have a file with two work sheet, in 1 sheet have monthly allowance to staff in 2nd sheet I need the data in a schedule format. Please see the attached file, my formula not working here properly.
View 6 Replies
View Related
Apr 28, 2009
Let's say for instance I have a vlookup that displays a the quantity of available items in stock pulled from a column in another workbook. Occasionally these numbers are negatives as they are backordered I would like to display these values as zero.
View 2 Replies
View Related
Jan 18, 2008
I am using =max(D:D), & need to return the cell address (using address)
However, then i need to combine this into my vlookup
I have been trying -
=VLOOKUP(ADDRESS(MAX(D:D),4),D:E,2,0)
View 9 Replies
View Related
Mar 24, 2009
LEFT(C4,3)
VLOOKUP(C4,C4:D38,2,FALSE)
VLOOKUP(LEFT(C4,3),C4:E38,2,FALSE)
I combined the first two formulas into one (the third one) but it is not working. I get an N/A result.
View 9 Replies
View Related
Jan 26, 2006
Is there any way to combine the VLOOKUP and IF Statement formula's into one
formula or is there any other kind of formula which does both?
View 9 Replies
View Related
Nov 27, 2008
I'm looking for a formula to return the correct value without using any help-column and it should be typed in no more than one cell.
View 10 Replies
View Related
Jan 29, 2009
I think I need a macro to do this. Can I use vlookup function for a range of number? lets say I have number 1001 to 1005. i want to combine the result. any other way other than using this formula
=vlookup(1001,table,2,false)+vlookup(1002,table,2,false)+vlookup(1003,table,2,false)+vlookup(1004,ta ble,2,false)+vlookup(1005,table,2,false)
Is there possibility to define a function that works like say =clookup(numlow,numhigh,table,2,false)?
View 8 Replies
View Related
Sep 30, 2009
I have a workbook with two sheets that I need to combine some of the data from. Sheet1 is a template to be used for translation purposes on Sheet2. I would like to put Sales Rep Info in column C on Sheet2 (has only Sales Rep ID) using Sheet1 as a translation table (shows Sales Rep ID and Sales Rep Info). I can do this with a vlookup function but would like to know how to do it in a macro so I can use on other more complex workbooks. Here is a sample data set.
View 3 Replies
View Related
May 23, 2013
I want to combine both an 'if' and a 'vlookup' within a single cell. So I want to say if the value in cell a1 can be found in columns c1:d100 then return a 'yes' if not return a 'no'. How do I do this
View 7 Replies
View Related
Sep 27, 2009
What i want to do is to look up of the value of home and away games seperatly. if there is no match for the lookup i get the "N/A" and if there is no value i get "0". If i get 0 the formula will calculate as a lost game.
View 10 Replies
View Related
Oct 2, 2008
XL 2003
I want to combine these three functions
=VLOOKUP(e4,AGTNAME!$A$1:$E$3086,5,0)
=LEFT(E4,FIND("/",E4)-1)
and iserror
View 9 Replies
View Related
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
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
