Lookup Multiple Arrays Return Text Or Numeric?
Jan 23, 2012
Is there a formula to look in multiple arrays and return whatever it finds i.e. text or numeric values?
Sheet1 ABCDEFGH1Cat0 CatFive Donkey32 Mouse2 Wolf43
4WolfFour CatFive Donkey35 Mouse2 WolfFourSpreadsheet FormulasCellFormulaB1=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,VLOOKUP(A1,$D$1:$E$2,2,0),
VLOOKUP(A1,$G$1:$H$2,2,0)))B4=LOOKUP(REPT("z",255),CHOOSE({1,2,3},0,
VLOOKUP(A4,$D$4:$E$5,2,0),VLOOKUP(A4,$G$4:$H$5,2,0)))
View 9 Replies
ADVERTISEMENT
May 28, 2014
I need creating a formula for the following situation.
I need to link multiple arrays from different sheets. But am having a hard time concocting a formula that will work.
I have 2 columns of data on sheet1. For example:
A-------------B
apples-----1245
oranges----456
nuts--------384
etc.
Now, I need a formula that will sum the entire column B, by looking up the array of column A on another sheet and only summing the numbers on sheet 1 that also have a value of "West" on sheet 2. Sheet 2 looks like this:
A-------------B
apples------West
oranges-----East
nuts---------West
This needs to be functional as a template to be used each month - the entire point of the formula is to save time and eliminate the current (and lengthy method that is in use). I can tweak the formulas as needed. But am trying to avoid having to concatenate anything or do any modifications to the format of the sheets.
View 1 Replies
View Related
Aug 6, 2014
I'm looking to look up sheet name based on a date, then look the date up within that sheet which is a merged cell, then return the figure in the corresponding cell.
I have a sample worksheet to attach, just have to work out how to do it.!
In the sheet "Cashflow Summary" in cell E24, i want to lookup the sheet name based on the value in C22 (merged cell), then lookup the date in that sheet (6 August 2014), and return the value in cell y8.
The difficulty is with firstly the merged cells, but also performing the lookup over a number of different arrays, as the sheets for each month, have the months listed under each other in rows of 5 days at a time.
View 3 Replies
View Related
Oct 31, 2009
i want to do is import multiple text files,with fixed arrays (luckily it's standar )
I have the piece of code that i currently automatically importing these .txt files,although it's for one per turn.Here it is though,to see the arrays
View 6 Replies
View Related
Mar 15, 2007
Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"
I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.
Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
View 9 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
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 30, 2009
I have a spreadsheet similiar to the below:
COLUMN A.....COLUMN B
Person 1.......Process 1
Person 1.......Process 1
Person 1.......Process 2
Person 1.......Process 1
Person 1.......Process 2
Person 1.......Process 3
Person 2.......Process 1
Person 2.......Process 4
Person 2.......Process 1
and so on.
In the list, the "People" aren't neccessarily in order, for example, later in the list, Person 1 appears again. There are a total of 10 processes, and any person can do any number of each.
What I ideally want is in Columns D, E, and F, is something like the following:
COLUMN D.....COLUMN E....COLUMN F
Person 1...........3.............Process 1
Person 1...........2.............Process 2
Person 1...........1.............Process 3
Person 2...........2.............Process 1
so a summary of the main data, showing how many each process each person has got.
I have, and failed miserably, getting arrays to work but that might just be down to my own incompetence. I have researched into Countif's and Lookups, but it's not quite working.
The data in columns D, E, and F needs to be fairly compact so ideally I wouldnt want any spare rows between the end of the summary data about Person 1 and the start of Person's 2 details.
View 9 Replies
View Related
Oct 12, 2012
how do I search the whole text of one cell in a particular column to another cell in a different column? I then want to return the full contents of the cell that contains the text I want to find. Ie. if I want to search the text "fidelity", cell A1 in column B which contains a cell with text "fidelity asset management". I want to return fielity asset management.
View 7 Replies
View Related
Sep 15, 2006
Im trying to create a formula that check to see if the value in H4 equal any value in the vlookup table m4:m48, if so, then Y, if not then N. the following does not work: =IF(ISNA(MATCH(H4,M$4:M$48)),"N","Y")
View 2 Replies
View Related
Mar 5, 2008
I have a database of customers that are all sorted by a customer number.
I have a form that has a number of fields that display customer info. When I use a blank of this form and put the customer number in it's correct field, all the other info fields do a vlookup on the customer number in the database file and return the info, such as phone numbers, name, address, etc.
Here's my question;
How can I check for a blank (using ISBLANK, I assume) to check to see if the customer number exists in our database and, if it's NOT blank, operate on it with the following example which is used to return their insurance expiration date.
=IF((VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))<TODAY(),"EXPIRED",VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))
I realize that this may be a sledgehammer approach and that Access is probably a better tool, but my company has not shelled out the $$ for Access...yet.
View 4 Replies
View Related
Mar 31, 2014
I want to look up a particular value and return according results horizontally and vertically. Attached is the excel.
View 10 Replies
View Related
Apr 14, 2014
How to write formula of following condition:
Input date on left column and the cell on right will automatically look up this date from the list located in other spreadsheet and return multiple corresponding in sequence order? Once another date is entered on left column, it will do the same function again on right column?
View 5 Replies
View Related
May 4, 2014
I have been using this formula to do lookup and return values
"=INDEX(ResultsColumn,SMALL(INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),),COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1)))"
I'm using this formula in a roster scene to pick up people that call in sick and display their restored job in a different cell. The problem that I am having say for example employee one calls in sick at 2 pm and i assign that job in a corresponding cell to another, then employee 2 calls in sick for the 1pm shift (the call was made after i have already restored employee one a replacement), the formula automatically places the 1 pm in the cell above the 2pm.
Is there a way to stop it from changing the value once a value is entered in a corresponding cell?
Link to the original formula thread. [URL] ........
View 10 Replies
View Related
Nov 11, 2008
Is there a way to use a lookup formula to return all values of a given text, not just the value of the first listing of the text. In an attempt to clarify my already confusing request, if A5 on "worksheet1" is "Land" I want E5 on "worksheet1" to return the summation of values assigned to "Land" "worksheet2" "column B" any time "Land" appears on "Worksheet2" "Column A".
View 2 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
Aug 17, 2007
I have an excel worksheet that consists of 4 tabs. Each tabs has approximately 60,000 lines of data. The data consists of user id's and menus that correspond to the user id. There are 131 user id's in total. I would like to be able to return all the menus for one of the user id's. If we need to start with one tab and work from there, that's fine.
View 9 Replies
View Related
Jul 22, 2009
I am trying to return a value from table below (column 4) based on values in the 1st 3 columns. I have data with the 1st 3 columns but need to lookup each and return the 4th so I have tried some formula's with if and vlookup statements but keep getting lost....
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
Dec 21, 2006
I am trying to make a search form in Excel 2003 where I can lookup data for a specific value which may have multiple rows. I have several columns, column A Tracking Number, B Order number, C Item Number, etc.
For example; Order number S6001 has three item numbers on rows 1,2,and 3.
I can get the data on row 1 using the basic "LOOKUP" function but not rows 2 and 3. How do I have all of the line items (rows) returned for that order number.
View 3 Replies
View Related
Jul 18, 2007
In Sheet1 are unique VM entries (Column A) which have a Group (Column B), Time (Column C) and a Reference (Column D) which can be same for other VM entries. In Sheet2 are the unique Reference Entries (Column A) which need Group (Column B) and Time (Column C) entries populated from Sheet 1. Multiple occureneces need to go in one cell, I realise this is not the best idea, but for this purpose they need to be and seperated by a comma or semi-colon.
In B2 I've used this formula:
=INDEX(Sheet1!A1:D11,MATCH(A2,Sheet1!D1:D11,0),2)
Which partly works, but only returns the first occurrence of H1 on Sheet1. I need all occurrences (i.e G1;G4;G8) returning for B2.
I can only think that some VBA script may be needed but I'm not really an expert in this area. In the full spreadsheet there are hundreds of rows and multiple entries, where Group and Time need to be populated on the second sheet
View 3 Replies
View Related
Jan 29, 2010
I need to look within column A (which contains text strings), match label in column c (which contains text for label), and return result within column B (titled category label).
Essentially, I want to label my data based upon a specific element found within column A with a label list in column C.
I have attached an excel file that shows the current problem. Nested if(isnumber(search( statements can perform this, but I yield too many arguments error after three labels?
Isn't there a way to do this with Vlookup or Hlookup?
View 9 Replies
View Related
Aug 13, 2008
(1) I've created a list in A2, which looks up F1:F27. I'd like to place an IF or Vlookup function in B2, that returns the corresponding cell from range G1:G27 based on selection in A2.
(2) Also, is it possible to include the fill coulour based on selections in A2 and B2?
View 11 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
Jan 20, 2014
I am wanting to use a vlookup or perhaps an array(?) to bring through multiple cell values based on being the maximum value. So say there are multiple employees (all with unique ids) under one manager (also with unique ID), I want to find the employee with the highest value in a particular column and return that employees details, such as name, employee number and the value itself, which are all in the same row. I have attached a basic example of the data involved.
View 8 Replies
View Related
Dec 1, 2009
I am trying to create an automated receipt for a list of artists. I have given the artist an individual reference so I am doing a VLOOKUP to return description/price value etc but I can only get it to return the initial value and I have approx 10-15 works under each reference. Maybe VLOOKUP is the wrong formula to use.. I have attached the file.
View 5 Replies
View Related
Jan 18, 2010
In the attached sheet, I want to look up values from table (A1:G5) and then for each occurrence of a particular number, return the value in the same row in column H, and sum when there are multiple values (see green shaded area for desired output. For example, the number 8 occurs in E3, E4, and G5. I want to return the sum of the corresponding values in column H (1,5, and 2). I've spent hours trying to work this one out, I'm sure this is probably an array formula, but I can't manage to make it work.
View 3 Replies
View Related
Sep 28, 2009
I am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing.
So, If PRODUCT_A with OPTION_A, then PRICE = $X.
If PRODUCT_A with OPTION_B, then PRICE = $Y.
If PRODUCT_B with OPTION_C, then PRICE = $Z.
etc.
I want the user to select the product, then the option, and have the correct price "pre-fill" the cell.
View 3 Replies
View Related
Dec 11, 2008
What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.
Please see attachment.
Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)
View 6 Replies
View Related
Jan 5, 2007
the formular that i have in the attached file is returing all the customer numbers
Is it possible to return only one value of each
the data is in the "data" sheet and the formulars are in sheet1
View 3 Replies
View Related
