Lookup Formula: Find And Return Value
Sep 30, 2009
I have a column with 11 different numbering schemes that go from 736466.01 through 736466.11 In the group from 736466.01 through 736466.07 I need to have the adjacent column look to those cells and if it finds 736466.01-.07 return "CM" if it is 736466.08-.11 I need it to return "TC". I created the formula below but it doesn't like something I have done unless I change the format of the 736466.xx to text. Anything other than 736466.01-.11 should return a "null" or "void" Can you help me improve this formula, or show me a better way of creating it?
=LOOKUP(E2,{"736466.01","736466.02","736466.03","736466.04","736466.05","736466.06","736466.07","736 466.08","736466.09","736466.10","736466.11"},{"CM","CM","CM","CM","CM","CM","CM","TC","TC","TC","TC" })
View 4 Replies
ADVERTISEMENT
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
Oct 18, 2013
I have two spreadsheets.
spreadsheet 1:
Lookup from Order numbers listed from A5:A177.
requested formula in I5: I would like a lookup to sheet 2 based on the order number (F19:F191), to return the cell above the first non-blank value.
spreadsheet 2:
Lookup value:Order number listed from F19:F191.
Data search:AY19:CI191
return the (date) which is in the range above the data search from row AY18:CI18.
I've had a look at few forums but i'm getting mixed responses, having to use index / match / lookup / min / --.
View 6 Replies
View Related
Apr 6, 2009
I have rows with text and numbers. In order to ensure that the numbers are accurate, I have a "QC formula" that calculates a check using all of the numbers from 1 row. The challenge is that the "QC formula" needs to vary depending on a text value within the row.
How can I lookup up the text value and then return the correct active formula for that row? I have too many differet text values to do a nested If statement. see simplified example below.
Condition ABCFormula' Needed based on Condition
Red123A*B*C
Blue123A+B+C
Green123(A+B)*C
View 13 Replies
View Related
Feb 17, 2010
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies
View Related
Jan 28, 2009
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies
View Related
Jan 26, 2009
I have a spreadsheet that is comprised of 3 columns:
Column A - a list of values
Column B - the rank of the value in the adjacent cell in column A out of all values in column A
Column C - the quartile rank (1, 2, 3 or 4) of the value in the adjacent cell in column A
I would like to create a formula that would return the highest value in column A that is ranked in the 2nd quartile.
View 3 Replies
View Related
Apr 1, 2014
I am trying to use a formula similar to VLOOKUP() to look in a table for two values and if both values are found, return another value.
Sheet2 is an example of my output file, sheet one is a generated file. i would like the vlookup formula in sheet 2 cell D2 and down to be something like this,
=VLOOKUP(A2,Sheet1!A:K,10)
But I would like to lookup A2 and B2, and the row that contains both values, return the value in the J column
View 3 Replies
View Related
Dec 8, 2013
I am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.
For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.
I have used this formula:
=IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]],
SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]]
='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")
I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.
View 3 Replies
View Related
Nov 10, 2009
in which the OP was trying to find the maximum date (Col B) in which the value in Col A occurred, DonkeyOte used this formula as a possible solution
View 4 Replies
View Related
Jun 11, 2014
In the attached sample work book Col E has text that I want to check if it is also in Col G and return Yes or No into Col F
View 4 Replies
View Related
Feb 26, 2012
I'm trying to find a formula that will find an original entry using 4 criteria original entry is cases ordered.
columns A,B,C,D will have to match then give me a result in column E to find original entry if there is one otherwise I will have to enter new row and original entry for cases ordered.
Eg.
A = section
B = description
C = size
D = region
E = cases ordered
Row 2 = fruit, apples, medium, north, 25
Row 45 = fruit, apples, medium, north,
View 3 Replies
View Related
Mar 24, 2014
Column A:
pants A green
Pants B red
Jacket C green
Jacket D blue
Polo C white
....
I need a formula to find some value( in our case "colors" = "green, red, blue, white" ... = 20-30 values), and to return in column B, the values, like:
Column A: Column B:
pants A green | green
Pants B red | red
Modern green Jacket | green
blue winter jacket | blue
Polo white D | white
I have different product feeds (csv) and i will like to search for colors in the product names.
View 2 Replies
View Related
Sep 6, 2013
Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?
View 9 Replies
View Related
Oct 26, 2009
I have a spreadsheet with multiple columns. (Sheet1) The heading row is my customer name. (Sheet1) My column are the dates. On another spreadsheet, I would like to find the customer in Sheet2 in Sheet1, then do an index to find the relevant data I need, returned in Sheet2.
Sheet2: find customer "ABC" in Sheet1, if found, then find the date that also matches Sheet1 and Sheet2, and return data "apple" .
sheet1
DATE ABC DEF
2007-04-01 £55.60 £61.13
2007-04-02 £56.33 £60.27
2007-04-03 £52.14 £62.58
2007-04-04 £51.69 £61.28
sheet2
CUSTOMER DATE RATE
DEF 2007-04-04 This should be £61.28
ABC 2007-04-02 This should be £56.33
View 3 Replies
View Related
Jul 7, 2014
Sheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
View 3 Replies
View Related
Feb 17, 2009
I have a formula that needs some tweaking. This formula is to reference the ACCT and find the “best” contact information and return the result to the Merge sheet. If the ACCT does not have a Parent then you reference the ACCT to the abc_Phonelist sheet. If the ACCT does have a Parent then use the Parent ACCT since it has a more desirable account number to reference against the abc_Phonelist. If the ACCT or the Parent ACCT uses Processors then the ACCT from the Processors sheet has the best reliable account number to reference against the abc_Phonelist.
In sum, there are three different possible “number tiers” that can be used. The first, the ACCT phone number. This means that the ACCT has neither a Parent nor a Processor. The second, the parent ACCT phone number. This means that the ACCT has a Parent, but neither the Parent ACCT nor the ACCT has a Processor.The third, the Processor phone number. This means that either the ACCT or the Parent ACCT has a Processor phone number.....
View 2 Replies
View Related
Dec 26, 2013
table1.jpg
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
View 3 Replies
View Related
Jun 29, 2009
I have a list of records each with a postcode that takes either of the two formats:
SA1 6HU
N4 3HF
I also have a list of postcodes that show only the first part of postcodes
SA1
SA2
N3
N4
What formula would look up "SA1 6HU" and return SA1? while being also able to look up "N4 3HF" and return N4?
View 2 Replies
View Related
Oct 8, 2008
I have this vlookup formula and as you can see when I run it works good. The only problem is that I would like to have formula that it something is not found I don't get #N/A , but rather a 0 or blank.
=VLOOKUP(A6,GL!A$1:E$76,5,FALSE)
LOOKUPDIVDPTCC SALES 46120
010775010775 49,028,244.58
010200010200 3,373,145.76
010700010700 113,667.00
010300010300 14,382.54
011000011000 (1,518,076.05)
010600010600 589.98
011900011900 6,280.31
010900010900#N/A
011100011100 313.08
010750010750 570.90
View 5 Replies
View Related
Jun 15, 2009
Column A lists types of employees (managers, engineers, sales reps, etc.), each of which earns a seperate hourly rate. Column C lists hours worked, and Column D lists the total pay (Cx*Bx).
So column B is missing. Is there a way to generate the hourly rates in column B for each employee type in collumn A, i.e. if it's an Engineer, return $29, if it's a Technician, return $18, without an infinite set of True/Fasle conditions.
View 5 Replies
View Related
Sep 11, 2009
I have two files with the below data:
File A
Bob Jones
Williams, K.
File B
Jones, Bob
Williams, Ken
The only commonality between the two files is the last name. I can easily separate the last name off File B, since it is always 'last name, first name'. I want a lookup formula to use the last name from File B (after I separate it), lookup to File A, and if it finds a cell in a specified column that contains the same last name, return data from another column within File A.
View 3 Replies
View Related
Oct 13, 2008
I am trying to look up a value using VBA on 1 sheet and return it to a cell on another sheet.... I was hoping Evaluate sum product would work but I can't get it to. Basically I want to look up values in column C and D to locate the row then return value in column I specify.. below eg where Column C = SYD and column D = SHA, row = 6 .... value in L row 6 = 1
View 2 Replies
View Related
Sep 20, 2006
My Lookup formula gives back N/A rather than an empty cell. I was unable to find the solution in frequently asked questions. Everything points to using VLookup which I feel doesn't fit my situation as I am only looking at one row of text.
The length of my targeted range changes so I am wanting to use the lookup for an extra amount of rows to ensure I capture all the data at any given time. Once my lookup gets to the end of the list and finds a blank cell i get back N/A. I tried to include "" in my formula to combat this but it isn't working. I have been using IF statements elsewhere on my sheet but there are 30 arguments and I was forced to find an alternative.
This is an abridge version of my formula:
=LOOKUP(Data!A2,{"","0129","0133","0163","0167","0187","0197","0202","0212"},{"","0201-0129","0205-0133","0201-0163","0213-0167","0201-0187","0216-0197","0212-0202","0217-0212"})
Didn't know if I was supposed to use [code] for that. Figured better safe than deleted
View 9 Replies
View Related
Jun 17, 2008
I am trying to set-up a formula that will look up two different values of a grid (x & y) and the result will be the intersection of the two values (z). If there is not a value at the intersecting cell i will have it display "n/a". I have attached an example of what i am trying to do but i have no idea how to set up the formula. I forgot to mention that the index or chart above remains constant but the lookups do change. I believe i may have figured it out. Here is what i came up with for cell C19. Should i have done anything different? =INDEX($A$2:$E$8,MATCH(A19,$A$2:$A$8,0),MATCH(B19,$A$2:$E$2,0))
View 8 Replies
View Related
Dec 18, 2013
How to return the value of what i looking up. for example i want to find the value of A1 from a range of B1:B10 if A1 is B1 then it return the value beside B1 for example the value of B1 is C1
View 2 Replies
View Related
Nov 19, 2008
Hi there, please can you help me transfer data from 1 spreadsheet to another (I have attached a file which has 2 sheets).
I am trying to populate columns G to L (sheet B) with data from column H (sheet A) - the lookup is between 'Material' & '7410','7090','7680','7060','8630','7580' on sheet B & material & plant on sheet A.
I have tried H&VLOOKUP & INDEX/MATCH but I keep returning a #N/A..
View 10 Replies
View Related
Oct 1, 2009
I have a worksheet with the following values:
header row 15 17 19 25 etc.
each column underneath the row will have a value of 0 or -1
A B C D E etc
1 15 17 19 25
2 0 -1 -1 0
3 -1 0 0 0
4 0 0 0 0
etc
I want a formula that I would put in each row that would return the value of Row 1 if there is a -1 below that value. So, in this example:
Row 2 formula would return 17,19
Row 3 formula would return 15
Row 4 formula would return nothing
View 9 Replies
View Related
Aug 7, 2013
I am trying to automate the account classification of credit card (cc) expenses. The problem is charges to USAirways will always have random numbers after the vendor name on the cc download. I set USAIRWAYS as a Travel Expense and a simple Vlookup will not work.
For example, let's say a data dump in column C of the CC:
cell C2 is USAIRWAYS 037232A49429
CeLL C3:
USAIRWAYS 037282hf39647
Cell C4:
Facebook A8476H4
In column E I have my simple vendor name. Column F as the Vendor Account. For example E1 is USAIRWAYS. F1 is Travel Expense. E2 is FaceBook. F2 is Marketing Expense
In cell D2 & D3 I want to return Travel Expense anytime the formula sees USAirways in cell C2 returning the answer in column F after matching w/ column E. In cell D4 I want to return Marketing Expense.
View 3 Replies
View Related
Jan 4, 2014
I have a list of names in sheet1 column A and 3 more list of names on sheet2, sheet3 and sheet3. What I'm trying to do is find a match for each name on sheet1 column A on the other sheets and depending on which sheet it was found, I need a specific value returned to sheet1 column B.
For example, if A1 was found on sheet2, then on B1 I would need to return the value "Morning" but if it was found on sheet3, then I need the value "Afternoon" and if found on sheet3 I need the value "evening" returned to B1 and then the same for every other name on the list found on sheet1 column A.
What I tried was this.
This goes in Sheet1 B1 (then I would drag it down to search for the rest)
=IF(AND(MATCH(A1,'sheet2'!$E:$E,0)),"Morning",IF(AND(MATCH(A1,'sheet3'!$E:$E,0)),
"Afternoon", IF(AND(MATCH(A1,'sheet4'!$E:$E,0)),"Evening","Un-assigned"))
Now the problem with this is that it only works if there is a match on sheet2 but if there is no match, it just returns "N/A" and it does not move on to sheet3 and/or sheet4.
I also tried with vLOOKUP instead of MATCH and the same thing happens.
I also tried this and the only thing I get is "Un-Assigned"
=IF(AND(A1='sheet2'!E:E),"Morning",IF(AND(A1='sheet3'!E:E),"Afternoon",IF(AND(A1='sheet4'!E:E),"Evening","Un-Assigned")))
View 3 Replies
View Related
