Lookup And Match 2 Values Within 2 Columns
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
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 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
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
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
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
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
Aug 23, 2008
I have 2 reports with the same column headings. I want Excel to compare each one on the common OrderID and then just show me the ones that don't match--either one invoice can't be found on the other, or in the cases where they are found, the invoice amounts do not match (IOW create an exception report).
View 2 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 12, 2014
I need a formula for left coulmn lookup (using index and matching function) and lookup array has duplicate value.
View 1 Replies
View Related
Jun 9, 2014
Here's my table:
2014 2014 2014 2013 2013 2013
Jan Feb Mar Jan Feb Mar
A 56 63 100 78 25 44
B 48 12 74 32 65 97
C 45 85 96 42 82 73
How will I use index match function to get the data that correspond to each criteria? For example I am looking for the value of A in Mar 2013.
View 4 Replies
View Related
Jan 14, 2012
Is there any option to match different values with the same "look up Value"?
For example.. I want to match the account number with the according amount. suppose, In the sheet 1, there is account number 1234 with amount $1, $2,$3 and in the sheet 2 there is same account number 1234 with amount $2 & $3. but I have got only one amount although it was not in the sheet 2 i.e. $1 with the vlookup formula. Thus, I am not able to find out the exact amount according to the particular account number.
I need to match more than 3000 this type of repeated data.
View 9 Replies
View Related
May 30, 2007
I'm trying to do in Excel 2000 that seemed way easier before I tried to write the formula. Any thoughts would be much appreciated.
I have some data setup more or less like this:
1State DeptJan Feb Mar
2California 50128 31 6
3Utah 20119 3 11
4California 20122 49 22
5New York 301 29 51 15
I'm looking to write a formula to retrieve the numerical value located in the cell for a particular month, across from a particular department number and state - so I can plug it into another spreadsheet.
I tried the following formula for January, but I get an "N/A" error when I hit 'Ctrl+Shift+Enter' to use it...
=INDEX(C2:C5,MATCH(1,("Utah"=A2:A5)*("201"=B2:B5),0))
Can you not use static values in this type of formula?
View 9 Replies
View Related
Feb 14, 2014
I am building a timeclock, and am using a user form to allow people to sign in/out.
I have a punch in form that logs the inputed username and password (after being checked against my employee list), along with a count of their username on this sheet (as a count of how many times they have punched in) and a timestamp (column 4). These values are all pasted to the first blank row in my worksheet "PunchInTimes".
Then, I have a punch out button that logs these same values the same way, only on "PunchOutTimes". Then, the idea is to link the index number of the PunchOutTimes record and PunchInTimes record for any given user, so that when they punch out, a complete record is pasted (to the first blank row) in a separate worksheet called "Timesheets".
I have been able to paste the username and count to the "Timesheets" sheet, however as soon as I try to index/match the count and inputed username (on the punch out form), I get an error 13 type mismatch.
I'm trying to paste the corresponding punch in time to this punch out time, so ive used an index/match with two columns (username and count) found in "PunchInTimes", and try to return the corresponding 4th column value (punch in time). After pasting the "punch out only" values to the PunchOutTimes, i used a "With/End With" statement, and set wsc equal to my 'timesheets' worksheet at the beginning of my code. So the line where I get my error is below in bold:
[Code] ......
View 4 Replies
View Related
Dec 10, 2007
Working on an export from an e-commerce application that lists the item purchased, unfortunately not by item #, but rather a lengthy description. I'm trying to construct an item number based on that description which contains several constants.
Each item ordered appears in a single row, comprised of ORDERID, QTY, PRODUCT DESCRIPTION
I have several tables to pick out PRODUCT TYPE, COLOR, SIZE, etc. I need a formula to search to see which of the unique values appears in each in the cell and return the shortcut of that value.
PRODUCT DESCRIPTION example "Sweater - Blue - Large - Wool"
TYPE TABLE:
Sweater SWTR
T-Shirt TSHT
Jeans JEANS
COLOR TABLE:
Blue BL
Green GN
Purple PL
Black BK
I would have separate tables for each part of the description.
The formula I'm seeking would search the PRODUCT DESCRIPTION cell for the range of values for each 'part'. So it would search to see which of Sweater, T-Shirt, or Jeans was in the cell and then return the one that was - SWTR in this example. I would modify this formula for each 'part', in its own cell, and then use an =cell#&"-"&cell#&etc... to combine these returned values.
I know I can write a large if- chain using ISERROR & FIND, though some of these lists will get lengthy.
View 9 Replies
View Related
May 28, 2008
I need to create a macro that will loop through a list of names on Sheet 1 / Column A, and if that name is listed on Sheet 2 / Column A, then it will set Sheet 1 Column B (for that one name) to "1".
The problem I've run in to is that both the lists change in length each time I have to run this (so no set range).
I've been able to get this to work with two ( nested) loops, where the first loop goes through the Sheet 1 list and the nested Loop looks for the name on Sheet2, but it is extremely slow.
Is there any simple VBA code or formula that I'm missing that could do this? I've tried to do something with the " Find" VBA command, but I can't see how it would apply to this if the list changes in size.
View 8 Replies
View Related
May 20, 2014
I am trying to do an INDEX/MATCH to return a value based on multiple lookup_values.
The workbook is attached, and what I am trying to accomplish is this:
If 50, 75, or 90 are selected from Column D drop down of this (Main) worksheet, then return Column A, B, and C values for those rows to the Report worksheet.
So in my sample spreadsheet, John, Jim, and Jack's information should be returned on the Report worksheet because their Column D shows 50, 75, and 90. Alice, Wanda, and Cindy's information should not be returned on the Report worksheet because their column D shows Budget Only, Contacting, and 100.
My current INDEX/MATCH formula is =INDEX(Main!$C2,MATCH(50,Main!$D2),0), but this only works when column D has 50 chosen because I have only asked MATCH to return info if it finds something with 50 because I do not know how to ask it to search for multiple lookup_values.
So, as you will see, only John's information is returned on the report worksheet, but Jim and Jack's are not.
View 7 Replies
View Related
Jul 1, 2008
I have two excel sheets. The first sheet has a column with codes and another column with dates. There are more than one date for the same code. The second sheet has just one unique code. I would like to match the code on the second sheet with the code on the first sheet and return ALL the dates associated with that code to the second sheet - horizontally.
View 5 Replies
View Related
May 2, 2006
I have one excel file which is kind of a database. Now I need to lookup two columns in that file, currently, they are columns A and G, and then copy the value in column H on that row to another worksheet. Because the "database" file is pretty big, I cannot simply go through each row and compare the values. Is there an easier way for this? I checked other posts in this forum and notice other people suggested "lookup" function, but I don't exactly understand that answer.
View 2 Replies
View Related
Jul 11, 2012
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies
View Related
May 19, 2013
I have 4 columns A B C D
A and C have dates , B and D have values
Column A has dates listed verticaly from 7-1-1954 to present
Column C has dates lited verticaly from 7-1-1954 to present but without weekends and holidays
I want to know if there is a way to match the dates and values from columns C and D to the columns A and B, with the objective of eleminating the extra cells that contain different dates?! I need to compare the data day by day and the additional weekends/holiday data makes it not line up!?
7/5/1956
2.75
7/5/1956
47.8
[Code]...
View 3 Replies
View Related
Dec 5, 2006
I have attached a file with an example of what I need. The data tab show the data has been given to me. I need it in a different format to be able to load it into our system. The Needs tab shows what format I need. Basically, I need to convert this table into a flat file, where there is a record for each "X" value only.
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
Mar 9, 2012
Been a while since I've worked with macros within excel and I can't seem to get what I remember being a basic macro to work whatsoever. I have 2 worksheets containing a massive amount of data and need to pull some cells from one into the other when values in 2 columns match.
To better explain, sheet1 has ID numbers in column G spanning for roughly 1700 rows. Sheet 2 has corresponding ID numbers in column EO. The data I need to copy over is in columns EP and EQ on sheet 2.
So I'm trying to build a macro to compare the values in Sheet1_Column_G to those on Sheet2_Column_EO and when a match is found, copy the value in EO and the adjacent entries in columns EP & EQ over into columns X, Y, and Z on sheet 1.
Couldn't get a VB function together to save my life so I tried working with MATCH & INDEX and didn't get very far either. I've included my current function below.
=IF(ISNUMBER(MATCH(G1606,Data!$EO$527:$EO$601,0)),INDEX(Data!$EO$527:$EQ$601,MATCH(G1606,Data!$EO$527:$EO$601,0)),"Not Found")
View 7 Replies
View Related
Mar 26, 2008
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies
View Related
Jan 2, 2009
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
View 7 Replies
View Related
Jan 31, 2008
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
View 9 Replies
View Related
Jun 10, 2009
Is it possible to return a value or a sum of values using segmented lookup criteria. The Sample 1 spreadsheet (attached) explains it better.
View 10 Replies
View Related
May 18, 2007
im trying to get the lookup answer based on two values the current formula i have is
=VLOOKUP(AND(A1,B1),Sheet2!$A$1:$C$31,3,FALSE)
which naturally returns N/A... i might even be using the wrong formula?
View 2 Replies
View Related
