Combining IF And Either Vlookup Or Index
Apr 17, 2009
I think I need to combine functions to allow me to automate data entry from a table from another worksheet. If the Name entered in column A on Exp Breakdown matches a name in column A of Salary Breakdown I cannot seem to write a function to scan the table in Salary breakdown and lookup the percentage that person is paid from a specific program number. I have attached a sample worksheet to hopefully make it easier to understand.
View 3 Replies
ADVERTISEMENT
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
Jul 13, 2014
I am trying to use following string to find result into column F and unable to find reason for Error.
=INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))
A
B
C
D
E
F
1
part
code
price
find part
find code
Result
2
x
11
5
y
12
#VALUE!
[Code] ........
View 1 Replies
View Related
Jul 15, 2009
I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:
A1 contains B
B1 contains 2
B2 contains "tribbles"
An imaginary function might go like this........
View 3 Replies
View Related
Oct 3, 2011
I think I need to combine a vlookup and sumif, but am struggling.
I need a formula to total the values in 'Drivers & Standings'!AB3:AB37 where 'Drivers & Standings'!D3:D37 matches A2
View 2 Replies
View Related
Oct 27, 2009
I have a set of data consisting of 3 columns.
Column A - Clinic Names (Alpha Numeric)
Column B - Task (Alpha Numeric)
Column C - Output (Numeric)
There are 52 clinics and each clinic has to perform the same 36 tasks
example:
Clinic A Task 1 #
Clinic A Task 2 ##
Clinc A Task 3 ###
Clinic B Task 1 #####
Clinic B Task 2 ######
I would like to seach for a clinic then search for a particular task and then display the output
I tried a Vlookup with a nested Match and that did not work.
View 10 Replies
View Related
Sep 11, 2009
I am using the following formula to calulate the compounded rate of return for a series of monthly returns: {=(PRODUCT(1+(b1:b100)/100)-1)*100}
I would like to be able to integrate a VLOOKUP function into the above formula so that it looks at the dates in column A and then apply the PRODUCT function above to the return values in column B that fall between start and end dates specified in cells C1 and D1.
View 4 Replies
View Related
Nov 19, 2013
I am trying to create one formula that can be copied to other cells to get varied results.
Here are the formulas
1. =IF('Calculated Rental Fees'!H4='Daily Rentals & Charges'!$B$28,'Daily Rentals & Charges'!$B$30,VLOOKUP('Daily Rentals & Charges'!$A$20,LATECHRGETBLE,2,FALSE)*C4
2. =IF(AND(I4>='Daily Rentals & Charges'!$A$36),I4='Daily Rentals & Charges'!$A$38),VLOOKUP('Daily Rentals & Charges'!$A$22,LATECHRGETBLE,2,FALSE)*C4)
Basically i am trying to calculate late fees for a rental company using the if and vlookup functions.
View 2 Replies
View Related
Oct 8, 2009
I have this table
.......A.....B.....C....D
1.....I......a.....d.....g
2.....II.....b.....e.....h
3.....III....c.....f......i
As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i
I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on
I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.
View 9 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
Jan 13, 2009
I have tried the Vlookup formula, and also the index/match formules but I can't get my desired outcome. As you can see the setup is fairly simple. For turnover I want to lookup the values based on Period AND profit center This last bit is my problem, how to define a double criterium.
The idea is to choose the wanted latest estimate (cell B1) and that for turnover, discount etc the values are based on 2 criteria (periode and profit center)
I have also added the desired outcome to clarify my question.
View 5 Replies
View Related
Oct 22, 2009
I have a worksheet that has numerical data in A1:A22. In E1:E61 I have set values in numerical order and in F1:F61 I have a letter or letters that correspond to E1:61.
I am trying to write a formula in B1 that will look at the value in A1 and then match it up in E1:E61 and then return the corresponding letter in F1:F61.
e.g: in A1 i have an INDEX and MATCH formula
View 2 Replies
View Related
Jan 27, 2010
I tried with the VLookup, and HLookup and neither one worked for me. I have a reference table with all the data, and then input sheet where I want to bring this data by using one of the excel function. ie. When I enter office name on the top of the input page, I would like the Tax ID # in cell C3 to fill in using the excel function from the data on the reference tab. Same with cell C14 & C15. see attached for an example.
View 5 Replies
View Related
Jul 6, 2012
I have data on two separate tabs. Data in columns A of both tabs potentially match. I am trying to identify and pull the value in column C of the second tab where the values in columns A match and the first instance where the value in column B on the second tab is less than the value in column B of the first tab. The values in both columns B are dates.
column A first tab = column A second tab
column B first tab < column B second tab (first instance of less than)
pull value from column C on second tab to appropriate row of column C on first tab.
View 1 Replies
View Related
Apr 3, 2008
I have two worksheets that I am hoping to use to generate one report in excel. Sheet1 contains the following information for my entire work center:
Benefitor Account Dollar Value Document #
Sheet2 contains a list of Benefitors that are relevant to only me.
I want to pull the information from Sheet1 where the benefitors on Sheet1 match the benefitors listed on Sheet2 into Sheet3.
View 11 Replies
View Related
Dec 4, 2008
I have a tab that has Employee Name in one column and the benefit they chose in another column. The employee name is repeated a number of times since they have chosen more than one benefit.
I am trying to create a new tab where the employee name in is the first column (only once) and each benefit is listed in separate columns along the top. I want to put an "x" in the column for the benefit the employee has chosen.
Is there a way to say "look for this employee, see if this benefit is what they chose, and if it is, put an "x", if not, leave blank"?
View 9 Replies
View Related
Jun 5, 2009
I am trying to get the info shown on table 1 into table 2. I suspect index and match is the answer, but I am not sure.
Table 1 Employee # Deduction Amount 101 AD&D 7.00 101 LIFE 4.00 101 LTD 12.00 101 STD 6.00 101 HEALTH 300.00 101 DENTAL 25.00 105 AD&D 8.00 105 LIFE 6.00 105 LTD 6.00 105 STD 300.00 105 HEALTH 25.00 105 DENTAL 8.00 112 AD&D 7.00 112 LIFE 5.00 112 LTD 13.00 112 STD 5.00 112 HEALTH 450.00 112 DENTAL 37.00 Table 2 AD&D LIFE LTD STD HEALTH DENTAL 101 105 112
View 9 Replies
View Related
May 1, 2014
If I have a column of data with a bunch of values (which can't be sorted & which is constantly changing so cant be broken into another column and then sorted) what formula do I need to output the second occurrence of a value in that column?
View 3 Replies
View Related
Jul 8, 2014
I would like to create a spreadsheet with 3 columns: employee ID, last name, first name. Then on the 4th column I would like to enter the employee ID and have the last name and first name auto populate into the 5th and 6th columns. How do I do this?
View 14 Replies
View Related
Mar 3, 2014
Attached example file has data with which I need to do the following;
On the tab "part list" I put in the part number in column F. Column C should then be populated automatically by looking up column F in the tab "Lookup" and showing the value found in column B of that tab.
The difficulty with this is, is that when the part number in column F starts with "DN", the lookup value should end with "FD/A" and when the part number in column F starts with "Z", the lookup value should end with "TY/A".
This last part I can't get it to work. I've tried vlookup with a index/match function inside, but it won't work somehow...
View 3 Replies
View Related
Jan 22, 2010
Can Vlookup use multiple column index. How can I solve my problem depicted in attached file? Is there any solution?
View 6 Replies
View Related
Dec 25, 2007
I have used in Index & Match formula in column G as follows :
=IF(ISNA(INDEX(R:R,MATCH(E13,S:S,0))),"",(INDEX(R:R,MATCH(E13,S:S,0))))
The codes in columns R & S are as follows:
******** ******************** ************************************************************************>Microsoft Excel - M_NECPE.Man Accts.xls___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)boutR1=RSTU1New CodesOld Codes 2 310100199999V 4101002NEW 51010032270A 61010061100 7101009NEW 81010101104 91010111101P 101010121101S 111010141102 121010151102L 131010181130 141010222203 151010231106I Sheet1 [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.
The code in column E & G is as follows:
******** ******************** ************************************************************************>Microsoft Excel - M_NECPE.Man Accts.xls___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)boutG62H62G63H63G64H64G65H65G66H66G67H67G68H68G69H69G70H70G71H71G72H72G73H73G74H74=EFGH621106B| 631100|101006 641101D| 651101K| 661101P|101011 671101S|101012 681102|101014 691105R| 701103|101037 711104|101010 721105| 731106|101031 741106I|101023 Sheet1 [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 have used a lookup table to calculate the value in column H as follows, but cannot get it to work
=IF(ISERR(VLOOKUP($E62,$M$3:$O$850,3))=1," ",VLOOKUP($E62,$M$3:$O$850,3))
View 9 Replies
View Related
Apr 21, 2009
VLOOKUP($L4,'[mail.xls] new'!$A$6:$AB$261,6,FALSE)
Which gives me proper result...but the problem i m facing is i have many columns after L4 like M4, N4, O4 and so on....where i have to get the result.
So for that every time i have to paste the above formula and manually change the col_index_num i.e. 6 then 7,8,9 in every column where i use the above vlookup. So is there anyway that it will append the col_num_index by 1....i.e.
View 9 Replies
View Related
May 7, 2009
I want to create a formula that will use a database table as a lookup for index/match and vlookup type functions.
I have my ODBC connection connecting to a mysql database and am successfully able to run sql queries through excel.
Is there a way to get index/match/vlookup formulas to use the database table as the lookup range?
View 9 Replies
View Related
Dec 4, 2009
I am trying to find out whether it is possible to reference to a sheet index number, instead of sheet name, in a VLOOKUP formula.
For instance, normally I would write:
VLOOKUP(A1,'ExampleSheet'!A1:B10,2,False)
(I hope got the translation to English language Excel correct)
Let's say that the sheet index of Examplesheet is 2 (at least it is 2 the way VBA sees it).
Is there a way I could reference sheet number 2 in the VLOOKUP formula, instead of its name? My sheet names vary, but the formula should look in the same area, regardless of name.
View 9 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
Feb 28, 2010
I have the following arrangement
PVCu_Woodgrain_DoorF190210300#REF!
PVCu _woodgrain_Door is a drop down list that selects from a number of named ranges.
The Cell that shows (F1) and is called DOORSTYLE with 6 choices F1, F2 ... F6 (I cannot change these to numeric only values)
300 is the 2 previous cells added together. It is called DRSZ1 and will match a numeric value in the named array.
My formula cell with the issue has this formula;
=INDEX(INDIRECT(ARRAYDR1),MATCH(DOORSTYLE,INDEX(INDIRECT(ARRAYDR1),0,1)),MATCH(DRSZ1,INDEX(INDIRECT(ARRAYDR1),1,0)))
I know MATCH(DOORSTYLE,INDEX etc. should be a VLOOKUP to find F1 in the named array, but I just cannot get it right.
View 9 Replies
View Related
Jun 9, 2014
Getting a formula that will generate the corresponding rating attached to a row value and columns header as per below example:
EnglishSS1 EnglishSS2 EnglishSS3 Rating
10 20 30 3
20 30 40 2
30 40 50 1
Where my variables would be based on whether a person is under EnglishSS1 and getting a score of <=10, the resulting rating would be 3. I know this is doable by doing a vlookup with a range lookup value of false. However I have roughly 70 column headers and it will be a bit taxing to combine an If and Vlookup statement to address it.
I have attached a sample sheet for reference : Book1.xlsx‎
View 2 Replies
View Related
Aug 14, 2014
What I want to do is the following, I have two sheets, one where the data needs to be filled and the second where the date needs to be looked up. In Sheet1 I need to find a date for each of the NR2 and NR1 combination. But in the second sheet there are multiple NR1 occurences and also single occurences. So if there is only one, I need that date, if there are several I need the average of all the occurences for NR1, not taking into account the N/A ones.
(some examples from the file)
NR2 NR DATE
100707987121951
100702347121960
100707750121960
100707721121960
100702422121960
[code]....
So for example, NR1 121965 has two dates, 03/09/2002 and 27/01/2004, so here it should calculate the average of these two and put that average in the first sheet.
I was thinking of something like IF(MATCH(?) gives one result,put that with vlookup, else AVERAGE of all MATCH that are not N/A)
View 3 Replies
View Related
Oct 25, 2007
Is there a way to automatically change the column index number in the VLOOKUP formula when copying the formula to columns? For example, when I copy a VLOOKUP formula from column A to column B, the cell references will change, but the column index remains the same. I'd like the column index to be increased by 1.
View 2 Replies
View Related
