I want vlookup to return results based on the *** and age of the person from tables in a separate sheet. For eg. if the person is "male" or "female" and if the person is "<=15", "16", or "17". I have been practicising with a formula that looks like this:
View 6 RepliesSo i have a spreadsheet that has a list of members and how many events they have attended. That is fine because i achieved this by doing a countif function on their account number. The spreadsheet has to sheets Events Attended TOTAl and List. In the list it has their name account number and what event they attended and what date. What i need is to have a function that will lookup their account number and return what event they attended but they might have been to 4 different events.
I have a total of 5 columns dedicated to Event attended so we can tell it to lookup first event and return result then have an if function in the next column to lookup event and if its returned in previous column move onto the next event attended.
I am finally at the end of this project. I need to find a code that will copy any lines that the person has turned 65 in the previous month or that will turn 65 in the current month to the Monthly tab.
Sample Macro jscalem.xlsx
The 3 vlookup will be in a single cell for concatenation like :
=vlookup1&"-"&vlookup2&"-"&vlookup3
Problem the are 3 vlookups where it will return names (with format -> Fname, Given Name Middle) and probably blank returns.
I have problem with the replace since there are also spaces between the Fname,Gname and Mname sample name with spaces.
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 RelatedI am attempting to create a formula that will, return text results based on 2 criterion.
This is what my sheet looks like
Name Job Full Time / Partime
Bob Admin Full Time
Barry Accounts Part Time
Chris Claims Full Time
Emma Claims Full Time
The formula I have for this is {=INDEX(A1:A4,MATCH(1,(((B1:B4)="Claims")*((C1:C4)="Full Time")),0))}
Which is fine and returns the name Chris, however as you can see from above, Emma also meets the criteria given however, I cannot get Emma to appear also. Is it possible to display more than one entry in a cell/s based on this formula, or am I missing something.
I would use a pivot table to do this, however I need the sheet to be completely automated.
I have a report that shows when our company has shipments covered by inventory (although you are not seeing the conditional formatting in the table). Columns K:Q represent shipment dates and shipment amounts. Columns D:I represent inventory with Column J being the total inventory. The conditional formatting works to highlight the cells in K:O of when shipments are covered by inventory. I need to be able to return the date (Columns K:Q) for an item (Column B) when a shipment is not covered by inventory.
For example Row 154: all shipments are covered by the amount of inventory. A shipment of 3 on 04/24 is more than covered the 105 pieces of inventory. But for Row 170, the shipment for 480 pieces on 04/23/12 does not have enough inventory to cover.
The result I need to return for row 170 is that 480 pieces are due on 04/23/12 that are not covered.
What formula would I used to return that value?
Excel 2010ABCDEFGHIJKLMNOPQ1LineITEMNEXT CUSTOMER PART WIP INVENTORYSTAGINGFINISHEDINSPECTDCYTRAILERTOTALPast4/18/20124/19/20124/20/20124/23/20124/24/20124/25/2012154Tabber105290325AA05290325AA-DC000010501053155Tabber1070025AC-EXP070025AC-
[Code]....
1) I have an invoice form, and need to create a formula that will allow me to calculate a discount IF an item number begins with "C" or "CE".
Example: Item # is in cell F12. (may or may not begin with "C" or "CE")
Item price is in cell J12.
Extended price (qty x price) is in cell K12.
In L12, I need to calculate a discount (from % in fixed cell L9) on the figure in K12 - based on whether or not the Item # in F12 begins with a C or CE.
If it does not begin with C or CE, then L12 needs to equal 100% of K12.
(The 2 parameters I referred to in the title were:
C, followed by a number
CE followed by a number )
2) The biggest part of the dilemma is that the person using this spreadsheet is totally unfamiliar with Excel formulas, spreadsheets in general, and has to send this finished product to a client each week. So I need this to be as simple as possible - which seems to me to be a formula (that can be copied to insert rows, etc. if necessary).
I am looking at developing a table that has three possible options for different categories, being Yes, No and N/A. A yes score a certain percentage depending on what column it is, and the use of N/A changes the percentage of other scores. (i.e. A yes in column 5.1 = a score of 5, a yes in 5.2 = 5, a yes in 5.3 = 10 and a yes in 5.4 = 15. However if 5.4 is N/A then this amount is diveded amongst the remaining 3, i.e. 5.1 = 10, etc.)
View 3 Replies View RelatedPlease see the attached sheets.
Here's the issue:
On sheet "master" I have a list of goals in column A.
Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet.
I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown.
I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either.
Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change.
how this done for AL, I can go ahead and create the other sheets.
Data set
Column A Column B
PriceCode1 5.36
Sheet 2 Data Set
Column A Column B Column C Column E
PriceCode1 5.02 10.01 313
PriceCode1 10.02 50.01 314
PriceCode1 50.02 100.01 315
Formula on Sheet 1:
{=INDEX('Price calculation'!$E$1:$E$70,MATCH(C3&D3,'Price calculation'!$A$1:$A$70&'Price calculation'!$B$1:$B$70,1))}
The issue I am having is that the value isn't an exact match so it is returning line 3 with a value of 315 rather than line 1 and a value of 313. What am I doing wrong?
OK i have put the membership cards on the same sheet as my raw data so to make the formulas easier. On the membership card i have under membership level i have the formula : =VLOOKUP($J$3,A:E,4,FALSE)
there is 3 types of level bronze, silver and gold
what i would like is if the level is gold after then an image to be placed in the cell rather than the word gold and a different image for silver and a different image for bronze.
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
I am trying to do a vlookup to 1 sheet based on customer number and return price. If no customer number than I want to do a vlookup based on dunnage number to return the price. This is what I have for a formula but it never returns a value if one is found in the first lookup.
=IFERROR(VLOOKUP(C4,'Dunnage Exception'!A:C,3,FALSE),IFERROR(VLOOKUP('Raw Data'!D4,'Dunnage Master'!A:C,3,FALSE),"0"))
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies View RelatedI have searched the forums to see if there is a similar prob already answered but to no avail (apologies if this has already been answered).
I am designing a sheet for a Uni project that needs to calculate the average attendance rates for students between age groups. My data has the age in a specific age for example:
Age
18
19
19
21
etc.
I am trying a SUMIF function that adds all attendance rates based on a criteria - however I can't get the criteria to fall between two ages ranges (more specifically, I need to sum the averages where the ages is between 21 - 25), as the function seems to only allow for one expression of criteria.
My function currently Reads:
=SUMIF(A1:A25, ">=21 & <=25", B1:B25)
Where A is the column recording ages and B is the column recording attendance rates. It presently returns 0.
Is there a better function for this task, or a way around the SUMIF criteria?
Need formula that will categorise ages. Column P contains the age of 4983 people. What I need to do is put these ages into categories in column Q:
0
1-4
5-9
10-14
15-19
20-24
25-29
30-34
35-39
40-44
45-49
50-54
55-59
60-64
65-69
70-74
75-79
80-84
85-89
90-94
95-99
100-104
105-108
So, if the person's age is 86 in column P2, column Q2 should read 85-89.
I am trying to find a formula to find out peoples ages from their date of birth but on different dates.I know you can do age from DOB 'today' or on a given date, but I have date of birth in column A and the date they were seen in column B and I would like their age in column C. Is this possible?
eg
Date of birth | Date seen | Age at date seen
01/05/1989 | 01/01/2011| ?
02/02/1956 | 02/03/2012| ?
05/02/1930 | 05/05/2009| ?
I have a macro which returns the results for
=IF(ISNA(VLOOKUP("EMPLOYEE",Z:AA,2,0)),0,VLOOKUP("EMPLOYEE",Z:AA,2,0))
As of today, the entry EMPLOYEE is sometimes written as it was EMPLOYEE, and sometimes as EMP. I have to add these two together. How do I change the code of the macro?
Before it was:
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(""[EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"
I tried "=IF(ISNA(VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"+
"IF(ISNA(VLOOKUP(""EMP"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMP"",C[-16]:C[-14],2,0))"
as I would in Excel, but of course it doesn't work.
I have this function trying to added 4 VLOOKUP results together, but it only works for 2 rows and all others show as #NA.
=VLOOKUP(A8,MA!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,PQ!$A$8:$H$10,3,FALSE)+VLOOKUP(A8,ON!$A$8:$H$19,3,FALS E)+VLOOKUP(A8,TW!$A$8:$H$22,3,FALSE)
I wan to create a table using 2 column of data.Below are the data
Type Name
A Danon
B Chris
C Hilton
A Jin
A King
B Ling
C Seng
D Aaron
B Halim
I wan to convert these data to a table which have four dimension. A, B, C, D. With Vlookup function, i can do it. But, i find difficulty when the result is overlap, when in the type A, got Danon, Jin, and King. My result only can lookup is King. Other cannot display.
Worksheet1 has data.
Worksheet2 uses VLOOKUP back to worksheet1 for data to populate cells on worksheet2.
The VLOOKUP command below returns a value of 126 to cell C6. I want to use the number result of the VLOOKUP in C6 (126) as part of a calculation for another cell. In thiscase, cell D6 is keyed in. I want to get the result of =(N(D6)-N(C6)) and put it in cell F6. To further complicate things, if C6 is blank, I want F6 to be blank.
VLOOKUP formula that returns a value of 126 from worksheet1 and puts it in cell C6 on worksheet2. It also returns a blank if there is a blank on worksheet1.
=IF(ISNA(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0))+(VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,0)=""),"",VLOOKUP(A6,Pivot_Table!$A$3:$D$36,3,FALSE))
Add together D3+G3+J3+M3+Q3+U3 and put the result into W3? I think the reason it wont work is because there will not always be a value in D3/G3 and J3/M3
New KS4 grades Markbook.xlsx
I have a large spreadsheet of data, with lab results by date. Sometimes, there are multiple data points for a single date.
I need to produce monthly reports that list all the results for specific tests in a given month.
So, for example:
Date SG Property Tank
1/5/12 1.015 567324 2044
3/15/12 1.002 568210 2103
3/18/12 1.025 568056 2044
3/18/12 1.036 565200 2102
4/1/12 1.019 566713 2103
I would like to make a report for March that looks like this:
Date SG Tank
3/15/12 1.002 2103
3/18/12 1.025 2044
3/18/12 1.036 2102
And the next month, make a report for April, then May... etc.
I tried to think of a way to do this using vlookup or index/match, but couldn't figure it out.I also tried using filters and then automating some kind of copy/paste, but there is an extra line between the heading and the data, so that the date column is filtered as text instead of date. The source data is not my spreadsheet, so that would be difficult to change.
I would like to do a lookup function, in which multiple results are returned.
i.e.
VLookup Result 1
Result 2
Result 3
Etc.
I know how to do it if there is only one answer but say I have a unique identifiers in which I want all the results in column B:B displayed one after another, say locations have numerous products sold/manufactured at it.
I would like a formula to calculate the following:
If either one of U8 or AP8 are blank return blank (both need to have a number in)
If sum of U8 & AP8
I have a table that has 6 columns and 5 rows. I need to check a name (division) against the column heading, then and return a classification from the first column based on the score.
Below is an example of what I am using (I'm not sure how to post a screen shot):
DIV1 DIV2DIV3DIV4DIV5
A91.7689.4198.82100.82102.35
B111.43108.57120.00122.00124.29
C141.82138.18152.73154.73158.18
D195.00190.00210.00212.00217.50
E195.10190.10210.10212.10217.60
DivisionDIV3
Total Score 155.6
ClassificationC
The formula in Col G uses the value in col's B:E. I would like a formula in Col G that will use the 4-digit value in Col F ( same values as Col B:E) and return the same results.
******** ******************** ************************************************************************>Microsoft Excel - PLAY 4 EVE MOSTLY.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF1G1F2G2F3G3F4G4F5G5F6G6=ABCDEFG111/10/0966666666Q211/09/0977667766DD311/10/0977767776T411/11/0972467246S511/12/0977667766DD611/13/0978667866DSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am looking for a formula that will yield the following: IFAH9>AI9 return the word Green, IFAH9=AI9 return the word Blue, IFAH9<AI9 return the word Yellow. If AI9 is blank stay blank.
View 3 Replies View RelatedI have a Sheet named Main, Juz and another called wordforword.
Currently on Main sheet I have a vlookup that displays the results from wordforword. Instead of showing the result, I would like the user to be directed to the wordforword sheet result, with the click of a hyperlink.
I do prefer formulas over macro if possible.