Instead of trying to explain my challenge, the attached workbook should be self explanatory. My answer is surrounded by the box. I need a formula that would automatically provide this output.
Lookup Scenario.xlsx‎
I am trying to replicate a payslip from a list of data on a worksheet.
The list of data contains the employee name, location they worked, and number of hours.
Each employee will work at multiple locations throughout the month, perhaps 10 or so.
The payslip must contain each location worked along with the relevant data, in a list so to speak.
What function can I use to pull this in? Of course if it were one location I would use vlookups to pull in data. As this only returns the top match I would then need a different formula to pull in the second location in the cell underneath?
I am using the formula =IF(OR(E2="COLD"),"33%")
Which changes my cell to show the text 33% if the text cold is entered into cell E2. Now what I would like to know, is if I can add multiple catch words to give alternate pre defined percentages. Such as warm and hot to give the respective answers as 66% and 99%
I'm trying to figure out how best to build a table to do:
IF A1="yes", "no", "na" and
IF B1="yes", "no","na" and
IF C1="yes","no","na" then
"AT" or "IC" or "VE"
So, I need a table that can look up responses to three questions, then lookup the corresponding answer (AT, IC or VE). Some of the combinations of anwers will yield the same anwer (i.e: yes, yes, yes=AT and yes,yes,no=also AT). I'm trying to come up with the most probable answer by evaluating these three questions; Thus; 10 combinations will yield an answer of "AT", 10 combo will yield "IC" and 7 combo will yield "VE". I've done this with 2 variables, but can't seem to get it to work with three...
Want a single count of multiple columns based on the columns selected value. Data is in text format.
Have tried multiple COUNTIF statements and have tried using pivot table (Excel 2010) both only give me total counts for all. I think I need an OR statement somewhere, but not sure where?
In other words, if a single record has an "any" in the any fields or a "yes" in the yes fields, I want to to count that as one record.
Sample data:
Pegnancy Smoke
Pregnancy Alcohol
Pregnancy Marijuana
Pregnancy Powder
Stress Cigarettes
Stress Marijuana
Stress Alcohol
Stress Medication
[Code] .....
here's the formula that I'm using: =INDEX($C$35:$AF$35,MATCH(MAX(C41:AF41),C41:AF41,0 )) where $C$35:$AF$35 contains names of people & C41:AF41 contains #'s. This works great if there isn't a tie. Is there a formula to search the range, find the max # and if there are two answers display both names. ex. the max# is 3 and both joe and sam have 3. it would then show joe / sam or something like that. Is this possible? If not, then if there is a tie for the max #, to have the cell just display tie.
View 4 Replies View RelatedI have a table whereby I Vlookup a different spreadsheet on an order number (in this instance Z011352/001). There are multiple sketch numbers associated to this Z number.
I would like to be able to input the Z number and then obviously the Vlookup pulls up the first sketch number. This works fine.
If I then input the same Z number again it pulls up the same sketch number, but I want it to look in the current table (the one i'm entering the Z number onto), see if the sketch number is already present and if it is pull up the next sketch number in the list on the separeate spreadhseet. (I hope this is clear enough)
My Vlookup code is:
=IF(C1070"",VLOOKUP(C1070,'[CrossingsSchedule.xls]Update Worksheet'!$E$1:$AC$10000,20,FALSE)," ")
I have a message box in my spreadsheet. I want it so if the user puts in multiple answers that certain message box appears, if they put in a different set of multiple answers it shows up. For example, the code below is what I am using now. It says that if the user types 5225 in C16 and 3000 in C17 then a specific msg box appears. I want it to say if the user types in 5225 or 5226 or 5227 the same msg box appears but if they type in 5665 a different msg box appears. How would I change this code? ...
View 9 Replies View RelatedIn the attachment, on the totals sheet I am doing a count of the results on Sheet2. Under "Alcohol as it Applies to Me" on Totals I am trying to count the 5 different categories, but the original question is a pick all that apply so at times there are multiple answers. I can't figure out the formula to count each phrase when there is multiple answers.
HRA Results.xlsx‎
On my spreadsheet i want to find the results from 2 criteria that i entered.
My search criteria are "Oostbos" and "AA8", and excel has to find this from another spreadsheet that i made for rostering.
OostbosN3 evelineAA8N3 evelineAA8N2 MargaAA7
The problem is that i have multiple shifts with the "AA8" criteria, but my function only finds the first one.
I used the following function:
Code:
=IF(C8="";"";INDEX('Afdeling PG PH'!$A$6:$A$28;MATCH(C8&$A$7;'Afdeling PG PH'!$B$6:$B$20&'Afdeling PG PH'!$AI$6:$AI$20;0)))
Also when the AA8 cel is empty that i doesn't show anything.
How the second N3 eveline, shows the 2nd result and so on.
I am currently maintaining a database that keeps up to date records of employees in my company and their vacations including their nationality dept. etc. for the vacation reports i have a "last day of work," "return to work date," "Actual Return to work date," and at the end a "remarks" column,
Moreover I need to report how many employees per department/Discipline are on leave ex. ( mechanical, electrical, and so on.) That I did using countifs having whoever is remarked as "na" vs. actual return date, Discipline vs. each discipline. All works fine but what i want to ask is there anyway that i can list the names of employees that are on vacation under each discipline?? Ie. if 3 are in the electrical engineering department, can i list their names? or if Today()>Actual Return to Work day (ie they are late and have not arrived yet) is there a way that it can list the names of multiple employees? rather than having to work against each name etc.
I have 2 worksheets, Worksheet 1 has Customer Magic Number on it as a reference and a few customer details and Worksheet 2 has Customer magic number and contact fields.
I am currently using the formula
=INDEX($C$3:$C$575, SMALL(IF(N2=$D$3:$D$575, ROW($D$3:$D$575)-MIN(ROW($D$3:$D$575))+1, ""), COLUMN(A1)))
to show the contact codes in sheet 1 however I also need to show the Notes which are located in Columns G:I, Is there an easy to use the index & match functions as above with the concatenate function to add the notes in the cell beside where I am inputting the contact codes?
I have a table in excel that i need to use to mark a 20 question quiz. I have the correct answers in one column and the students answers in the next column. I want to mark a correct answer with 1 and an incorrect answer with 0 marks. I know how to use IF, however the students answers can vary eg, correct answer could be B and D but the student writes B + D. Is there any way of marking this correct even tho it is not exactly written out correct?
I'm trying to cross-reference three sets of criteria to find my result; however, I can't get the VLOOKUP function to perform correctly for this need. Maybe I'm using the wrong function.
I attached a simple workbook to highlight my challenge. The over-simplified language that I included in the attachment, which highlights my problem, is as follows:
IF:
(B5=Sheet2!B4:B12) ---> This line finds the correct row to reference, on the Control sheet.
(year(C2)=Control!C3:E3) ---> This line chooses the correct annual column on the Control sheet, for the given month (e.g., use 2014 assumptions for a 2014 month).
(month(C2)=Control!E14) ---> This line counts the expense only in the month when the expense recurs (e.g., April 2014, April 2015).
THEN:
SUM(Control!C4:E12) ---> This line finds the specific number based on the criteria above.
In the attached file I have a sheet containing my data in A1 - D73. Column A contains a list of names, Column B contains a specific month, Column C contains a specific category and Column D contains the raw data.
Is it possible to create a formula similar to VLOOKUP to look not only at Column A, but to look at Column B as well in determining the value returned? I would like other users to first select a name and then select a month to view the data. I've attached a sample of what I've created so far. The original file contains 14 Names, 9 Months and 40 Categories.
I am trying to find a function that will scan down a column to find a particular criteria. I would like all the cells that meet this criteria to have their entire row pulled through to a new sheet.
For Example, In the attached spreadsheet, I would like everyone in sheet 1 to have their details pulled through to sheet 2 if they are in class 1, sheet 3 if they are in class 2, and sheet 4 if they are in class 3.
I'm using a v-lookup to pull information from one worksheet to another. I know that each of the cells has a match on the second sheet, but the calcuation is resulting in a Value Not Available error. The only way I've figured out to get past the error is to go into each cell, hit F2 and then enter.
View 2 Replies View RelatedI need creating a formula for the following situation.
I need to link multiple arrays from different sheets. But am having a hard time concocting a formula that will work.
I have 2 columns of data on sheet1. For example:
A-------------B
apples-----1245
oranges----456
nuts--------384
etc.
Now, I need a formula that will sum the entire column B, by looking up the array of column A on another sheet and only summing the numbers on sheet 1 that also have a value of "West" on sheet 2. Sheet 2 looks like this:
A-------------B
apples------West
oranges-----East
nuts---------West
This needs to be functional as a template to be used each month - the entire point of the formula is to save time and eliminate the current (and lengthy method that is in use). I can tweak the formulas as needed. But am trying to avoid having to concatenate anything or do any modifications to the format of the sheets.
I am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.
For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.
I have used this formula:
=IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]],
SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]]
='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")
I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.
Is there a lookup formula that will give me multiple items? so in my example attachment i want to look up risk from section A to give me the item in section B, but for risk 7 for example it has multiple items (a,b,c), how would i get all them? even better would be if its possible to have in seperate cells
View 4 Replies View RelatedI have a sheet, called "output", in which I need to complete column C "calculated values". I need to complete the table based upon a formula table (which is in sheet "formulas").
For the first row of data, cell C2, I need to take the price per order ($0.25; cell A2) and number of orders (40; cell B2) and copy data to cell B4 and B6, respectively.
Once the data has been copied to to cells B4 and B6 on the fomulas sheet, I need to copy the calculated value in Row N to the output sheet. Note that the value being copied from N can be N11, N12, N13, N14, or N15 (the one that is <> to null).
This formula works on 1 sheet but we are unable to make it work looking across multiple worksheets? Looking for a match to A3 in column G and wanting to pull the info from column E in the same row.
=IF('2'!G3:G271=A3, LOOKUP(A3,'2'!G3:G271,'2'!E3:E271),0)
I have created a spread sheet that uses the LOOKUP function to pull information from elsewhere in the sheet and automatically fill in a cell so that I can save time.
The spreadsheet has several columns with a single word or number but the column used for the lookup contains 2 words, 1 number and a “#” sign. I have used the “CONCATENATE” function to create a column that has all the words, the number and symbol and then my formula says to LOOKUP the value in the created column and return the number associated with that LOOKUP value. The problem is it will not give me the correct value. All cells with the formula return a value from the same row in the LOOKUP table no matter what the LOOKUP value..
I have used this exact same formula (copied and pasted it) in another place in the table with the LOOKUP information added by hand, not generated with the “CONCATENATE” function, and it works fine.
I need is spread across various rows. In a separate sheet I want to be able to lookup two identifiers and pull the number needed in the adjacent row.
The first identifier is a 3 letter character, the second is CURREVO and I need the number to the right of CURREVO. My problem is CURREVO is not always in the same column, but in relation to the 3 letter character is always in the same row.
A B C D E F GDMGCURREVO52011.25YTDREVO243085.00DTYCURREVO11892.50YTDREVO59783.50ECUCURREVI1943.00YTDREVI 5,541.25CURFRQI4.00EEGCURREVO32864.75YTDREVO205426.75EICCURREVO658761.26YTDREVO3507022.68EILCURREVO335741.70YTDREVO1720830.72ENTCURREVI161242.39YTDREVI638681.84CVLCURREVO796266.21YTDREVO4816890.98ENDCURREVI34479.19YTDREVI44074.54EYECURREVI11880.12YTDREVI 108,007.02(Null)MICCURREVO1098694.15YTDREVO5766072.54NEUCURREVI25251.90YTDREVI 158,236.60CURREVO207.00
i.e.
in a seperate sheet, I want to pull NEU, in that row I want to find CURREVO and bring back the adjacent number = 207.00
New to using Excel formula's and am trying to create an IF/LOOKUP formula to look at multiple sheets within a workbook and display the information within the 'compare' sheet.The yellow cells are where data will be entered.
What I am trying to achieve:
Type the store numbers on the compare sheet (B2 and D2). For the sheet attached I have put 190 (in B2) and 2012 (in D2)
B2 store shows the predicted and actual values of 190 in columns B & C
D2 store shows the predicted and actual values of 2012 in columns E & F
(No need to worry about variance and difference columns)
So, if I change the store numbers in B2 and D2 to any of the sheet numbers, I want it to display the correct info for that particular store within the compare sheet.
I have attempted a formula, which you can see... I have basically looked at some previous sheets that had IF and LOOKUP on it and tried to replicate that for my sheet, but with no luck.
I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.
Test2014.xlsx
How can I get a vertical lookup or sumIF formula to check multiple tabs for a given value?
Or - is there a way to specify the tab? For instance, put "Tab A" or "Tab B" in Cell A1, and have the lookup formula reference the value of Cell A1.
I have a data table that contains begin dates, end dates, and colors. The data is input starting with the earliest begin date. On a second worksheet, I have a listing of the years starting with the year of the first begin date and ending with the end of this year. I need to be able to pull any color choices associated with a year from the data table on the first worksheet. Some years may not be specifically listed in the data sheet as the person could have had the same color choice for several years. Sometimes a person could have more than one color choice in a year.
I am at a loss for how to get to the data I need. I created an example spreadsheet to demonstrate the data I am working with, and an example of what I need my results to look like in the end.
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.