Lookup Multiple Columns
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
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
Feb 7, 2014
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
View 6 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
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
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
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
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
Feb 20, 2008
I'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?
View 3 Replies
View Related
Nov 18, 2008
In column A I have a list of product names..(e.g....ABCD, JKLM....)...this is the "master product list" -- 1000 rows of products.
In columns C:G, I have additional product names, each column represents products sold by state. (each column lists between 100-250 products).
I want to do a VLOOKUP and I wanted to know what products that are in
A1:A1000, that is NOT listed in C:G?
My experience with VLOOKUP is limited...but I do know that I can only look in 1 column at a time.
How can I write it to look at the ENTIRE range? I suppose I can copy all the all the products to 1 column...?
View 6 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 9, 2008
I am looking to write a search function that searches through a whole document. The only examples I can find are of a could different search functions but they all are searching an specified individual column and even then I didn't really understand them. My worksheet has 6 different columns which are all different labels for a certain tool. I want the user to be able to input any of those 6 labels and have all the information in that row be returned.
View 4 Replies
View Related
Jan 1, 2010
I would like to put a formula (or make a macro) for Cell F7 (and all the other cells too) in sheet "Budget Sheet" that pulls the value $100 from cell G5 from sheet "Input Sheet" by looking through columns A-F for the account ID "5111100"
Obstacles:
- I (for the most part) cannot change the format of "Budget Sheet" or "Input Sheet"
- I cannot have the formula (or macro) just look down Column F because the actual file I am working with has the pertinent account IDs scattered through columns D-F (it was exported from another program).
Notes:
- I am using Excel 2003 SP3
- If necessary, I can make the reference to "5111100 · Bonus" if that would make things simpler
- Column G will always contain the dollar amount I need.
-I've been hitting the books trying to figure out how to get array formula working on this and cannot for the life of me get it to obey when I press ctrl-shift-enter.
View 2 Replies
View Related
Dec 11, 2012
I'm trying to find a way to:
Use a referenced lookup value from sheet "A", to return values, from several columns in sheet "B"
Things to note:
a) The lookup values sometimes repeat. I need all the associated values with each repetition as well.
b) The lookup values in sheet "A" are a comprehensive list, sheet "B" also contains some of these values but not all. Essentially, what I need to do is find a way to lookup each value in an account numbers column in sheet "A", against a different account numbers column in sheet "B".
If that value occurs in sheet "B" I want it to return the values from Columns X, Y, Z, (I want these values returned in sheet "A".
If that value does not occur in sheet B, the corresponding cells should remain blank.
If the lookup value occurs multiple times, I need all the corresponding values from each of X, Y, Z columns.
View 2 Replies
View Related
Jun 17, 2014
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
View 3 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
May 28, 2014
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
View 2 Replies
View Related
Oct 9, 2008
I have a problem with the formula that look up multiple records with the same values and return multiple corresponding values in ascending order. I am using Excel 2003 and it is a bit complicated to explain so I have attached a sample spreadsheet to show what I mean.
What I want was after I have sorted the occurrence value in column E based on column B and I want to correspond the Rank in column D based on column A in ascending order for the same occurrence value in column E.
Eg: There is two occurrences for number 1 at E3 and E4, and three occurrences for number 2 at E5, E6 and E7 in column E. Then the Rank for the first occurrence for number 1 in D3 should be ranking 6 and the second occurrence for number 1 in D4 should be ranking 7, so the Rank for the first occurrence for number 2 in D5 should be ranking 3, D6 should be ranking 4 and D7 should be ranking 9 based on column A and B, etc.
View 3 Replies
View Related
Dec 8, 2013
I am using the below array formula in G2 (that I then drag across) to show the score for all the times "mike" appears. I would like to match all the times "mike" OR "red" appears, so that the value in K2 is "99".
=INDEX($A$2:$C$9999,SMALL(IF($A$2:$A$9999=$E2,ROW($A$2:$A$9999)-1,"hh"),COLUMNS($G2:G2)),2)
A
B
C
D
E
F
G
H
I
J
K
L
1
name
score
color
[Code] ..........
View 9 Replies
View Related
Jun 15, 2008
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View 4 Replies
View Related
Apr 11, 2007
After going through multiple threads in the forums, I got this code to do a multiple VLOOKUP method.
=IF(E2+F2=0,"",INDEX(C2:C10,MATCH(1,(A2:A10=E2)*(B2:B10=F2),0)))
It works perfect on a sample sheet. But when im trying to implement it in a sheet with too much data, it always fails.
I have attached the sheet I am trying the formula on. I have grayed down the columns which needs formula's. The data is picked out from the second sheet.
This is how I have modified the formula to suit me..
=INDEX(Data!$G$2:$G$663,MATCH(1,(Data!$A$2:$A$663=$C2)*(LEFT(TEXT(Data!$E$2:$E$663,"mm/dd/yy hh:mm:ss"),8)=$I2),0))
<<Please note that all the dates and numbers in the sheet are in "text" format for ease of use>>
View 9 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
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
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
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
