Lookup Many Rows And Columns
Dec 29, 2002
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.)
View 13 Replies
ADVERTISEMENT
Apr 7, 2014
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.
View 3 Replies
View Related
Oct 8, 2013
I'm working on a database right now for our school, similar to a directory. What I want to do is be able to look up what items a staff member has been issued and when/if they were returned. What I have right now is one sheet with all the information about the staff that looks like the one below. I want to create a separate sheet where you could look up a staff member by last name and can find all the information in the array relative the the specific cell looked up.
Last Name
First Name
Position
Building
Items
So, on the "lookup" sheet, I want to be able to input either Doe and have the look up return cells B2:E6, or if I input Smith it should return cells B7:D11.
I've been experimenting with Vlookup, hlookup, index, and match but haven't been able to get exactly what I want..
View 8 Replies
View Related
Apr 28, 2007
I have a lookup table which needs to be able to retrieve data from columns as well as rows and not sure if this is possible.
My data is set up with an ID number, financial year and data across the columns e.g
ID F/Y Apples Pears
9999 2004/5 5 4
9999 2005/6 7 8
9881 2003/4 5 3
My lookup table will have the ID number field which will be manually entered to lookup the information on the data sheet. e.g
ID 9999
FY Apples Pears
2004/5 5 4
2005/6 7 8
I can't work out a way to get excel to only give me the number of apples and pears for that year for that ID number. Not all ID numbers will have the same number of financial years so I may need to use VBA.
View 7 Replies
View Related
Nov 24, 2008
I'm having difficulty finding a way to ask excel to lookup a value based on an array spanning multiple rows and columns. There's a mock example in the attached.
View 2 Replies
View Related
Jul 9, 2009
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 ....
View 9 Replies
View Related
Feb 15, 2007
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!
View 9 Replies
View Related
Jul 28, 2013
i need to mark some row (which has some content written in), mark other row(with data too) and switch/transpose them mutual. when i was trying transpose method, which is using for switching rowns and columns, it wrote me error, that data are overlapping. it means it cant work on same things (rows > rows, columns > columns).
View 14 Replies
View Related
May 16, 2014
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
View 4 Replies
View Related
Mar 16, 2014
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.
View 9 Replies
View Related
Aug 1, 2014
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.
View 4 Replies
View Related
Apr 1, 2009
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 Related
Aug 12, 2009
I 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.
View 3 Replies
View Related
Nov 16, 2012
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.
View 3 Replies
View Related
Jul 10, 2006
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.
View 2 Replies
View Related
Feb 19, 2007
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
View 9 Replies
View Related
Jan 1, 2008
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
View 3 Replies
View Related
Mar 28, 2014
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.
View 11 Replies
View Related
Sep 3, 2008
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.
View 9 Replies
View Related
Apr 27, 2009
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.
View 6 Replies
View Related
Oct 27, 2011
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)
View 9 Replies
View Related
Feb 9, 2012
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.
View 5 Replies
View Related
Mar 11, 2012
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.
View 6 Replies
View Related
Jun 26, 2012
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.
View 5 Replies
View Related
Dec 3, 2012
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 Related
Apr 16, 2014
I 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")
View 1 Replies
View Related
Oct 25, 2007
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.
View 9 Replies
View Related
Aug 4, 2008
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 Related
Nov 30, 2009
I 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.
View 9 Replies
View Related
Sep 21, 2006
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 Related
