Use HLOOKUP AND VLOOKUP - Or Macro (drop Down The Cells In B4 And D4 )

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


ADVERTISEMENT

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

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

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

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

Combine HLOOKUP And VLOOKUP To Return One Value

Jan 2, 2014

I need a formula that will combine hlookup and vlookup to return one value.

View 14 Replies View Related

VLOOKUP Or HLOOKUP With Multiple Criteria

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

HLOOKUP VLOOKUP INDEX & MATCH Etc

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

Perform VLookup And Hlookup At The Same Time

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

Dependent Values With HLookup / VLookup / Indirect

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

VLookup (or HLookup) Cell With Reference Rather Than Actual Number

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

Combining HLOOKUP AND VLOOKUP To Find Solutions In A Matrix Using 2 Variables

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

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 To Work With Cells Containing Formulas

Dec 8, 2013

I have a niggling problem with a worksheet when using Hlookup to return a value from a range of cells it is coming up with blanks when there is data in the range.

There is only ever one row cell containing text in the specified range and i need this to be returned in another column.

The problem with the hlookup formula i am using is that it will not work if cells contain formulas and in the range i am working on all cells contain formulas.

I know this formula works if there is text without a formula in the cell range, as soon as it hits a cell with a formula though it won't work.

I have attached the sheet : Book4.xlsx

From j138 there is one cell with text i wish to be returned in column q for each row. E.g., in q1 the first cell text returned would be khawatim. q2 should contain cantor etc etc.

View 7 Replies View Related

Moving On A Hlookup By A Number Of Cells Each Time?

Nov 2, 2009

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

View 7 Replies View Related

Using Two Or More Drop Downs With VLOOKUP

Mar 21, 2009

I have an analytics sheet where I have a client name chosen from a drop down via validation. The problem I am running into is that while I want information from a data table to populate a cell based on the selection from the validation, I also want the cell to populate based on a second drop down box (basically selecting a time frame). Take the example data table below:

Client Jan 2009 Jan 2009 Target etc etc
Client A 1111 1290
Client B 900 750

I would like the user to be able to select Client B and then, from a separate drop down, also choose, say, Jan 2009 Target and then another cell would populate with, in this case, 750.

View 9 Replies View Related

Drop Down Box / VLookup Not Giving Results

Jan 8, 2009

I am making a company wide estimate sheet and having trouble with the drop down box. I have a drop down to pick the branch, but when I name the source it only says branch, even though I named the data on the branch sheet. I would like to be able to pick the branch initials and have it populate the phone and fax number under the main heading but am getting a error there.

Also for the labor is it possible to have it insert the correct labor into the formulas at the labor lines according to which branch is picked from the drop down.

View 4 Replies View Related

Drop Down Lists And Vlookup- Or Match

Apr 19, 2006

I have already have my lists created.... Problem... in Cells A17:A62 I need
the list of employees names to display down the column by selecting the
number value in Cell A13...(A13 being a four digit crew code)... Each
employee has a four digit crew number associated with them.... so far I have
Cell G10 as a drop down list to select the crew name, which puts the crew
four digit number in A13. So now I need Cells A17:A62 to display the
employee names down the column by using the value in A13... does that make
sense?

View 14 Replies View Related

Vlookup Macro: Highlight The Cells In The In The Workbook

Jan 12, 2009

I have created a macro to open a workbook from another workbook and do a vlookup against the data in the source workbook. I then wanted to highlight the cells in the in the workbook that I'm doing the vlookup from, and to unhighlight any empty cells. I did this all without code using the macro recorder so that I could then study the code and learn something.

View 2 Replies View Related

Trigger Macro When Cells Changed By VLookup

Dec 6, 2013

Colouring calendar entries based on a date match... VBA to colour a cell on each line based on date match

The code works fine if you change the date in the cell and press enter however my date changes are triggered via a Vlookup which the code doesn't recognise has changed so doesn't trigger the macro.

Is there any way to amend the VBA code shown below so that it triggers the macro when dates are changed in column I via Vlookup?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Target.EntireRow.Interior.ColorIndex = xlNone

[Code] ......

View 3 Replies View Related

Drop Down Menu Based On Vlookup Results?

Feb 2, 2013

im trying to create a drop down menu that changes depending on the results of a vlookup. example: I have a vlookup function that populates a field with a product name based on the part number, however in some cases there are multiple products with the same part number. is there a way to create a drop down menu that contains all the product names shared by the same part number based on the part number that was entered?

View 1 Replies View Related

VLOOKUP Multiple Results In A Drop Down List

Feb 12, 2009

I am using the VLOOKUP function to pull multiple columns of information from another sheet in the same workbook based on a name. The sheet it is pulling from is a query. I am looking up product names, and in the query there may be multiple results that the function can find.

VLOOKUP only returns the first result that is found. What I am trying to do is get it to dump all the results into a drop down list in one cell. If I attach a drop down to one of the columns of information I am retrieving, then I can select which of the entrys to display, right? I think this should work I just don't know how to go about setting it up to do it.

How would I set up a VLOOKUP formula that attaches a drop down list containing all the results that the function finds?

View 7 Replies View Related

Drop Down List Based On The Result Of A Vlookup

Mar 5, 2010

I am trying to make a drop down list based on the result of a vlookup.

What i want to do is look into a table that has country name, depot station, but i have more than one depot per country so when i look up with vlookup i only get one result back, the table looks like this.

Country Country nameDepot code Depot name 1 GBUnited Kingdom STN Stansted 2 GBUnited Kingdom EDI Edinburgh 3 GB United Kingdom EMA East midlands 4 FR France
GNO Garanoa 5 FR France MRS Marseille

How can i look up GB or United Kingdom and get all the depots listed from that country.

View 9 Replies View Related

Vlookup/Macro: Regions In Sheet2, Average Of Next 6 Cells

Mar 2, 2010

I am trying to write a macro do the folowing

1. Vlookup Regions in Sheet2 (eg. AP-Asia/Pasific)
2. Check which row they are in
3. then take the average of next 6 cells (AVERAGE 1,310, 6,744 etc..)

View 9 Replies View Related

Vlookup To Access Data Based On A Drop-down List...

Nov 14, 2008

i have been looking around here and it seems like my problem is similar to many's regarding the vlookup function. to me, what i'm trying to do sounds simple enough, but it can't get it to work. i have a table that has three columns, Item Number, Item Description, and Amount.

Each item has it's own number, a corresponding item description, and ammount (obviously), but there are some repeats. What I want to do it input the Item Description, and have one field automatically pull up the Item number, and the next field, pull up the price. I am using a drop-down list for the Item Descriptions utilizing data validation. here is formula that i have been trying to use that is not working: =VLOOKUP(B2,Sheet1!$A$2:$C$18,1,FALSE). i have attached a trial worksheet that i am using to work things out on.

View 5 Replies View Related

Vlookup With Multiple Criteria: Creating A Drop Down List Showing Alternative Choices With Sex

Aug 22, 2007

I am trying to create a simple user interface type thing so that someone is able to select from drop down lists someones information, such as whether they are male or female, aged between 19-35 or 35-67, whether they are studying in a business area, legal or construction etc (there are 6 variables in total), This will then give the probability of success of the person passing this course based on probabilities which I have already worked out. I have worked out how to do the first stage of creating a drop down list showing alternative choices with Sex, Age etc in the data validation options, however:

There are 517 possible combinations, as in Male aged 19 to 35 studying Business (with other variables) or Male aged 19 to 35 studying Law (+ other variables) etc etc etc each with their own probability of success. Due to the long nature of writing out Male1935BusinessNorthWestWhiteBritishCollegeBrown I have rewritten it so it appears in the excel file as M1935BNWWBCB, which obviously wouldn't make any sense to someone if they had to select M 1935 B NW WB CB from drop down lists.

Along side the M1935BNWWBCB there is the probability of success specific to that type of person. So for example I could would have:
M 60%
M1935 64%
M1935B 35%....

View 8 Replies View Related

Pause Macro - Select From A Drop Down - Restart Macro

Dec 18, 2007

I am trying to pause a macro on a protected sheet, select 2 adjacent cells (initially protected), utilize an existing drop down box to select a name from the list, copy the name from the list into the range of cells, then re-start the macro.
I had no problem when there was just one name (see John Smith below). I tried to use the InputBox command but needed to actually type in the name.

ActiveSheet.Unprotect
Range("C27:D27").Select
'ActiveCell.FormulaR1C1 = "John Smith"
' Application.CutCopyMode = False
' ActiveSheet.Paste
'Range("c27:d27") = InputBox("Enter value")

View 4 Replies View Related

Drop Down Box In Cells

Jun 16, 2009

I am trying to create a drop-down box in cells D21 and D22 which are dependent on each other. From my understanding this cannot be done in Data Validation. For example cell D21 is Route Number and D22 is Route name. If someone knows the Route Name they would select it from the drop-down, then cell D21 would fill in the Route Number. Similarly if they know the Route Number the Route Name would fill in.

I am able to have one dependent on the other using Data Validation and VLOOKUP, however not dependent on each other.

View 6 Replies View Related

Drop Down List For 2 Cells

Apr 5, 2009

I have a form with 2 columns. in the 1st, we have the product and in the 2nd, we have the price.

I have my drop down list for column1 but I want column 2 to show the correct price when column 1 has a product.

View 4 Replies View Related







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