I m trying to figure out a formula to lookup Two columns(New Data) with Two more columns(Old Data).
Need to lookup values i.e. is Receipt No with Amount in the New data with the Receipt No & Amount in the old data"
Old Data
New Data
Receipt
Amt
[Code]....
i'd like when i lookup abt specific in range (4 Columns is my lookup range)
get the results in one column with skipping blanks Cells "Note" Blanks cells it contains fromulas with "" so it blanks but have formulas ....
I am trying to do a (what I thought was a) simple lookup involving 2 columns.
Pls see attached example. I am trying to find the "?" values, by matching column A&B of the input to the Data columns and returning the $$$ column.
I thought this wouldve been a piece of cake but its got me stumped!
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.
I'm trying to come up with a lookup formula that would look at Column A and Column B then return the matching result from column C. If I entered A in a cell it would return 11, but if I entered 1 it would still give me 11. how to do a lookup with two columns, but didn't get far.
lookup.JPG
Here is my requirement...
Oct-13
Nov-13
Dec-13
Item name
Cost
MRP
Qty
Cost
MRP
Qty
Cost
MRP
Qty
[Code] .....
I need to find the MRP value of stone clip for nov-13.
I have read through other similar topics and tried using the index and match function to lookup but been unable to make the formula work.
I am trying to use the lookup function to pull data from a table using criteria in two columns and one row. I have attached a spreadsheet for example.
I want to use criteria of Day (eg . 1), Total height (cm), and jar # (eg. 1). I inherited this table from a previous coworker and don't have time to organize the data better.
In the spreadsheet I want to return the value of 8.8 (highlighted in yellow). The criteria I want to use to lookup the value are highlighted in red.
Im trying to return the number value of how many contracts have been signed in a particular month. So in my example I want to look pick out say "IM" and check to see how many of those types have been signed in say "Jan-09" and return that as a number. Have tried various mixes of count, sum, countif and sumif but dont seem to be getting anywhere.
View 4 Replies View RelatedI have been already fighting for about a week with this problem. I attached the Excel-file. I have three input data and as output I want to get also three different data. For Vendor I use the VLOOKUP function. For agreement# I have used (not in this file) CONCATENATE by adding an extra column to the data table and then use VLOOKUP.
The actual problem is to find the price because it is based on the three input data. I can't seem to find a function for it. I've tried INDEX, MATCH, IF, nested them for as far as my knowledge reaches.
The problem lies for as far as I know in the del.date cell. For example, input date is:
Client#:912345, Product:E569831, del.date: 16.7. (July 16th). This input date should return cell G12.
A
B
C
Ap1
queens Carrots
Goja Apple
Ap2
fujja Apple
fujja Apple
Ca23
Goja Apple
queens Carrots
I tried to write a function so that it would search for a word from Column C in Column B, and then when it found it, give me a a combined result of Column A and B. For instance. I lookup up C1 (Goja Apple) in the range of all of column B; the function finds it in B3 (Goja Apple); then it returns the combined result A3 and B3 (Ca23 Goja Apple). I tried the =lookup, but I think the first column you select must be in ascending order, and that wont work for me.
My daily receiving list has Item# received that day e.g.
ReceiptList.xls
Item#
123
456
I need to compare this to a Data Base list that has 10000 item# , seller, and status (this data base can have repeated same item# but varied seller and therefore different status
e.g.
Ing.xls (Data Base file)
Item# Seller Status
123 ABC Approved
123 GMC Not Approved
456 ABC Pending
456 XYZ Not Approved
456 QRS Approved
etc. +10000 items
Now I need to compare the daily receipts to the data base file and pull all the sellers for those particular items and their status which like I said could be on many rows, one row, or not listed at all to the ReceiptList.xls(which I can then make sure that the received items are from an Approved source etc.)
I am trying to create a formula that will look in 3 columns containing 300 rows, and give an answer if the criteria in all 3 columns is met on a single row.
example: A B C
1 Blue Bag 60
2 Red Bag 100
3 Blue Bag 60
I want to check ALL the rows in the 3 columns that satisfy the criteria "Blue" and "Bag" and "60" and count how many times this occurs. I have tried COUNTIF but with no success, unless I am doing it wrong.
how does the formula change when i want a new column to "bring in" ( lookup?) a corresponding value from the second sheet when it sees that the names match?
For instance:
sheet1
FIRST LAST HEIGHT1
John Smith 5'9
sheet2
FIRST LAST HEIGHT2
John Smith 5'11
So, the conditional format would be a 4th column in the first sheet-- still comparing the first & last names-- but when they match it enters "HEIGHT2" into this 4th column.
This is still a conditional format, right? sorry if it doesn't belong here
i did find a thread concerning my question but the topic was closed & the OPs best answer had been edited & removed for some reason...
i'm comparing annual pro sports stats for a project & want to do a vlookup contingent on pairs of columns matching between two sheets. ie if a first AND last name in one sheet match a first and last name in a second sheet then fill in x statistic. i dont know VB or anything, but i assume this can be done w/ a pretty simple formula
attached small example
I am working on a workbook, it's not my workbook so I can't change how it's laid out.
I am trying to figure out a way that excel will check 2 different columns for a code that is manually entered, then enter the codes description in another cell.
It's basically a deposit and expense ledger.
One column has expense codes and names, the other deposit codes and names.
I want to be able to enter the code letter into a cell in the ledger and excel will enter the code description into another cell.
I have undertaken a project and have hit a brick wall.
I'll try and translate my request in the easiest way I can.
I have a piece of software that stores what software is installed on a machine when it connects to my domain.
I therefore have a list of software that is installed on machines that connect to my domain.
Each machine that connects to the domain has a unique identifier code.
At present I have about 500 different pieces of software on the various machines.
The unique identifier for the software installed on each machine is a combination of the application (column B), the program (column C) and the version (column D)
I wanted to give each seperate piece of software a unique ID number.
I was wanting to run a formula similar to the pseudocode below:
If $b$1="adobe" and $c$1="8.0.0.1" and $d$1="adobe reader", then "1",
elseif $b$1="adobe" and $c$1="7.0.0.1 and $d$1="adobe pro" then "2", else "no software ID"
I am thinking that if I can get every piece of software that is installed on the machines to have a software ID then I can create a software ID allowed list then I can create a formula to identify what laptops have software on the "allowed" and "banned" lists.
The problem at present is getting the sotfware to lookup what software ID they have as they need to check 3 columns with a lookup list.
I'm working on budgets (salaries). I have one main spreadsheet with all employee names listed - other spreadsheets are for each department. I need to bring the total salary from the department spreadsheets and would like to do this by looking up (or matching?) the names in the column from the main sheet to the department sheets and then putting the salary from the department spreadsheet to the main spreadsheet. In addition, I have several that have their salary split between two or more departments. On my department spreadsheet I have a column labeled % of salary for this department. On the main spreadsheet, I want to figure out a formula that will sum the percentages for a certain person (so I make sure I don't go over 100%).
For example - Joe Smith's name is on the main employee spreadsheet. He is also listed on 3 other department spreadsheets, with % of salary listed as 30% for department 1, 30% for department 2, and 40% for department 3. On the main spreadsheet I need to add up Joe's percentages from the department spreadsheets.
I need to make a VLOOKUP in a sheet which I do not own or control the layout of. Therefore I cannot add any columns in the source to assist me in my lookup.
My VLOOKUP needs to look at two values/columns since the value in one column is not a unique identifier. This is easy in the value I want to lookup but not so easy in the lookup range in the source sheet. Ideally I would like to write my formula something like this:
=VLOOKUP(A2&B2;CONCATENATE('[OtherWorkbook.xlsx]Sheet1'!$M$2:$M$9;'[OtherWorkbook.xlsx]Sheet1'!$N$2:$N$9);2;FALSE)
I have data like this:
Code:
500a
TRUE470b
440c
TRUE410d
TRUE380e
350f
[Code]...
and I want to look up an approximate value in Col2, say 310, and return it's next-highest friend from Col3, in this case "g". This is easy enough with INDEX and MATCH. But I want to take it one step further and only use those values which are approved by Col1 - so in this case I want 310 to ACTUALLY return "e"
I found some good information here which gets me close. The following works very well. It uses a boolean & operator to match two values at once, but it only works for exact matches. This example goes down the list and finds the first "sydney" which has an "x" and gives the result "h".
Code:
xmelbournea
sydneyb
xadelaidec
xmelbourned
sydneye
[Code]....
When I use this approach on data like in my first example it falls over, my guess is because the boolean & falls down before the MATCH function has a chance to accept an approximate match.
On my "Order" sheet, I enter an account no. that looks up the company name, address, etc on my "Accounts" sheet. Below the address info on the "Accounts" sheet is a list of the salespersons with their email address.
After entering the account no., I want to enter the salespersons name and automatically look up their email address. I can make the lookup function work if I tell the formula which column the salesperson is located in, but am lost how to write the formula to locate the salesperson based upon the column no. that contains their company info.
It's not pretty, but this works.
=IF(F1"",HLOOKUP(F1,ACCOUNTS!A1:Z100,ORDER!G9, FALSE),"")
F1 - contains the account no. entered on the order sheet.
Accounts!A1:Z100 - range containing columns of account info.
ORDER!G9 - This is the row number value I obtained by using a match function to lookup the Salespersons name entered in cell F8, then I add 1 to render the value of the row that contains the email address located directly below the salespersons name.
=(MATCH(F8,ACCOUNTS!B1:B100,FALSE)+1)
Herein lies my issue, the salesperson name will not always be in Column B
I can write another Match to obtain the column no, but then I am using another cell to hold a calculated value for my lookup statement.
ACCOUNTSABCD1CODACCT1ACCT2ACCT323 COMPANY 1COMPANY 2COMPANY 34ADDRESS ADDRESS ADDRESS 5 CITY STATE ZIPCITY STATE ZIPCITY STATE ZIP6TELEPHONETELEPHONETELEPHONE78910TOM JONESJANE DOEMARY SMITH11tjones@company1.comjdoe@company2.commsmith@company3.com12SALESPERSON 2SALESPERSON 213salesperson2@company1.comsalesperson2@company2.com14SALESPERSON 315salesperson3@company1.com
Excel 2010
In words, Find Mary Smiths email address, she works at the company with the account number in cell F1 on the order sheet.
Here are two sheets:
Sheet1
systemip1 ip2 ip3 ip4 ip5 ip6
system11.1.1.11.1.1.21.1.1.31.1.1.41.1.1.51.1.1.6
system22.2.2.22.2.2.32.2.2.42.2.2.52.2.2.62.2.2.7
system33.3.3.13.3.3.23.3.3.33.3.3.43.3.3.53.3.3.6
Sheet2
ip system
1.1.1.3
2.2.2.3
3.3.3.6
3.3.3.1
Sheet 1 has 7 columns(system,ip1,ip2,ip3,ip4,ip5,ip6 and ip7)
Sheet 2 has 2 columns (ip,system)
I have to fill column "system" in sheet 2 with "system" listed in column 1 of Sheet1.
In other words look for "ip" in Sheet2 in 6 columns of Sheet1 and return column 1 of sheet1 as value.
I am trying to look in B1:B100 for the Cell that matches A1 and C1:C100 for the cell that matches A2 then return the value from D1:D100 that meets the 2 criteria.
View 6 Replies View RelatedI want to search (sheet2 column A and B )for a match of (cell A2 and B1 of sheet2) and return value into sheet2 cell B2 Completed and if not found Outstanding
sheet1
A john
B Week 1
sheet2
A2 john
B1 Week 1
sheet2
B2 Completed
Here is a formula I had been using in a google spreadsheet but does not work in excel
=Iferror(If(Filter('DATABASE'!$C:$D;'DATABASE'!$C:$C=SPREADSHEET!$A2;'DATABASE'!$D:$D=SPREADSHEET!G$1)>1;"Completed";"Outstanding");"Outstanding")
I am trying to run a lookup on a rather large table.
Column A Column B Column C Column D Column E Column F
Postal code City Province Postal code City Province
I am trying to look up the City and Province based on the Postal code and can't figure out how to do this.
There are too many Postal codes to fit them all in Column A, I have tried V Lookup, Index Match and can't get it to work.
Please see the attached example of my problem. I have TIME in column A in minutes and % in Column B. What I would like to do is search the % column against the value inputed into E2 (in the example 52%) and find the corresponding TIME value (Col A). However, where there is no exact match of the E2 value I would like to select the next greater value. For example as there is no 55% in my example the formula would move on to 57.27% and record 21 in D2.
View 9 Replies View RelatedI have 2 worksheets with thousands of rows. I need to lookup 2 columns (customer account # and number of occurrence) and then return a 3rd column (type of occurrence).
How do I do this? Normally I use sumproduct but in this case I need the result to be the text value for the type of call, not a count.
I've been working this for ever and can't seem to figure out the best way to go about it. I have attached an example sheet. All I need to do is figure out the Dept #... which is listed in Row 1, Column F:H. I want to match the project numbers and then return the AA, BB, or CC in Column B.
View 9 Replies View RelatedI'm using an index array formula I learned here to lookup a value between 2 sheets in a workbook. I have had no trouble using it when matching just 2 pieces of information (first & last name), but now I need to make 3+ matches to get the value & the formula is no longer working.
When I check "show calculation steps" in the attached example, I can actually see that all 4 items have successfully matched across the 2 sheets but for some reason the formula is still not pulling in the desired value. Am I wrong to think that I can match more than 2 items w/ this kind of a formula?
I want to write a formula so that if an item in column A = " name" and column B = " date" (name and date to be selected from a drop-down box), then it will return the adjacent cells text in column C.
For example;
ColAColBColC
nick01-Marabc ltd
mick01-Mardeliver it
dan02-Mardiy
nick03-Margo ltd
mick04-Marfreebie
dan03-Marhelp ltd
If I select "nick" and "3/3/08", then the formula will return "go ltd". I know how to do filters, my aim is to create a master sheet and then for someone else to be able to select their name and a month. The month will then update a mocked up calendar (i.e. a 7 by 5 sheet where cell A1 = Date selected, A2 = Date+1, A3 = Date+2 etc). I've attached a basic mockup of what I want to do. If I select nick and march, the yellow highlighted cells should populate in C9 and E9 with abc ltd and go ltd, the others blank.
I have a data range set up in a step chart formation. This means there is a "Name" to the left and a time line of "values" to the right in quarterly increments (3 months at a time). Row 1 has the first month of each quarter and should be used to find the start date. Row 2 has the last month of each quarter and should be used to find the end date.
I need to find the first value from left to right and return the date directly above it in row 1.
Then....
I need to find the last value in the step chart and return the date directly above it in row 2
Example
Start Date..A...B...C....D...E...F
End Date....U...V...W...X...Y...Z
Value-1...........5...2....7........
Value-1
Start Date: B
End Date: X
Duration: = B - X
The reason i need this information is to create a Gantt Chart.