Lookup based on matrix values

I am struggling to develop one of those "copy down" functions that can 
produce a set of values.  In this simplified example, the formula needs to 
tell me the name from column A if there is a value in the remaining array 
(B1:D5).

Dave       1      2     3      
Bob         1      
Sarah             3
George    2
Sally               1

So the first instance of the formula would produce "Dave", the second would 
produce "Bob", the third ""George", the fourth is "Dave" again, followed by 
"Sarah", etc.  Yes, I need it to read the array down the first column before 
it moves on to the next column.

For what it's worth, here's what is not working for me: 
=INDEX($B$15:$B$68,SMALL(IF($C$15:$Z$68<>"",ROW($C$15:$N$68)-14),ROWS($B$70:H70))). 
 It's an INDEX function using the SMALL function to produce sequential values 
and the IF function to identify all the places where the array is populated.  
It's entered as an array function, of course.

Any ideas?  TIA
0
Utf
5/28/2010 1:44:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
574 Views

Similar Articles

[PageSpeed] 6

Andy,

This is no simple task and is easiest achieved by wrtting a custom function. 
Open your VB editor (Alt+F11). Insert a Module in your workbook. Copy the 
below function into the module. Now go back to your worksheet. You will see 
you can insert the NAMEFINDER function. The first argument is the column of 
names. the second argument is the ENTIRE number matrix excluding the names 
column. The function returns a COLUMN array so you must execute with 
Ctrl+Shift+Enter. I know this is not the copy down formula you were looking 
for but you need only extend the number of selected cells before you execute 
to achieve a similar result.

Function NAMEFINDER(ByVal Names_Range As Range, ByVal Matrix As Range) As 
Variant
    Dim Answer() As String
    Dim TheRows As Long, TheColumns As Long
    Dim i As Long, j As Long, z As Long
    z = 0
    TheRows = Matrix.Rows.Count
    TheColumns = Matrix.Columns.Count
    For j = 1 To TheColumns
        For i = 1 To TheRows
            If Matrix.Cells(i, j).Value <> "" Then
                ReDim Preserve Answer(z)
                Answer(z) = Names_Range(i).Value
                z = z + 1
            End If
        Next i
    Next j
    NAMEFINDER = Application.WorksheetFunction.Transpose(Answer)
End Function


-- 
If this helps, please click "Yes"
<><><><><><><><><><><>


"andy62" wrote:

> I am struggling to develop one of those "copy down" functions that can 
> produce a set of values.  In this simplified example, the formula needs to 
> tell me the name from column A if there is a value in the remaining array 
> (B1:D5).
> 
> Dave       1      2     3      
> Bob         1      
> Sarah             3
> George    2
> Sally               1
> 
> So the first instance of the formula would produce "Dave", the second would 
> produce "Bob", the third ""George", the fourth is "Dave" again, followed by 
> "Sarah", etc.  Yes, I need it to read the array down the first column before 
> it moves on to the next column.
> 
> For what it's worth, here's what is not working for me: 
> =INDEX($B$15:$B$68,SMALL(IF($C$15:$Z$68<>"",ROW($C$15:$N$68)-14),ROWS($B$70:H70))). 
>  It's an INDEX function using the SMALL function to produce sequential values 
> and the IF function to identify all the places where the array is populated.  
> It's entered as an array function, of course.
> 
> Any ideas?  TIA
0
Utf
5/28/2010 3:39:01 PM
Andy,

What you are trying to do is not simple and is best achieved by writing a 
custom function in VB. Open VB editor (Alt+F11). Insert a Module and copy the 
below code into the module. Now go back to your spreadsheet and you will see 
you can insert the NAMEFINDER function. The first argument is the column of 
names you have, the second is the ENTIRE matrix. The function returns a 
COLUMN array so you must select a vertical range, type the formula and 
execute with Ctrl+Shift+Enter. I know this is not the copy down result you 
needed but you can simply extend the number of selected cells before you 
execute the formula to achieve a similar effect.

Function NAMEFINDER(ByVal Names_Range As Range, ByVal Matrix As Range) As 
Variant
    Dim Answer() As String
    Dim TheRows As Long, TheColumns As Long
    Dim i As Long, j As Long, z As Long
    z = 0
    TheRows = Matrix.Rows.Count
    TheColumns = Matrix.Columns.Count
    For j = 1 To TheColumns
        For i = 1 To TheRows
            If Matrix.Cells(i, j).Value <> "" Then
                ReDim Preserve Answer(z)
                Answer(z) = Names_Range(i).Value
                z = z + 1
            End If
        Next i
    Next j
    NAMEFINDER = Application.WorksheetFunction.Transpose(Answer)
End Function

-- 
If this helps, please click "Yes"
<><><><><><><><><><><>


"andy62" wrote:

> I am struggling to develop one of those "copy down" functions that can 
> produce a set of values.  In this simplified example, the formula needs to 
> tell me the name from column A if there is a value in the remaining array 
> (B1:D5).
> 
> Dave       1      2     3      
> Bob         1      
> Sarah             3
> George    2
> Sally               1
> 
> So the first instance of the formula would produce "Dave", the second would 
> produce "Bob", the third ""George", the fourth is "Dave" again, followed by 
> "Sarah", etc.  Yes, I need it to read the array down the first column before 
> it moves on to the next column.
> 
> For what it's worth, here's what is not working for me: 
> =INDEX($B$15:$B$68,SMALL(IF($C$15:$Z$68<>"",ROW($C$15:$N$68)-14),ROWS($B$70:H70))). 
>  It's an INDEX function using the SMALL function to produce sequential values 
> and the IF function to identify all the places where the array is populated.  
> It's entered as an array function, of course.
> 
> Any ideas?  TIA
0
Utf
5/28/2010 3:54:01 PM
Reply:

Similar Artilces:

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Problem with Null value elimintation criteria
Access 2007 on Vista. I'm building a simple append query to add missing records to a table. It examines a list of entries, identifies which are not in the destination table, and adds them. Simple thus far. The problem comes when I add a criteria to the source side to ensure no blank entries are appended. Here's the SQL I'm trying to use: INSERT INTO tblAgents ( AgentID, AgentName ) SELECT qryAgentsImport.RecAgentID, First(qryAgentsImport.RecAgentName) AS FirstOfRecAgentName FROM tblAgents RIGHT JOIN qryAgentsImport ON tblAgents.AgentID = qryAgent...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

Selecting a range of cells based on the date.
Hi MS Office Help, I would like to know how to choose a selection of cells based on what month it is. For instance, if the month was january, excel would allow me to select cells A1-F1, which I could then use as a data source for a chart. If the month became march or april, excel would allow me to select cells C1-H1, D1-I1 respectively, and so on. This will allow me to vary my chart automatically as the month changes. Help is greatly appreciated. Hi Solomon, Use the Data - Autofilter -custom feature or Data - filter - text filters - custom depending on your version of...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

Find value for one cell from 2 different lists.
value for cell L55 when K33 has two options. K33 = Bags or sack and form that I need to get values from 2 different lists and a 3rd cell L13 has a volume to start from. I am using L13 as my starting cell. Formula is. =lookup(L13,A117:A191,H119:H191) for when K33 = sacks. and if K33 = bags. =lookup(L13,A117:A191,K117:K191). Regards Chris Sorry. How do I get it to work from both lists with one formula. Thanks Chris. "Chris" wrote: > value for cell L55 when K33 has two options. K33 = Bags or sack and form that > I need to get values from 2 different lists and...

internet based services policy
Today when i opened money i got a pop-up about microsoft money 2006 internet based services policy. I click Decline because i don't want it and i don't want microsoft to "from time to time without notice automatically install updates" and MONEY CLOSES!!!! WHAT THE HELL!!! I can't open it without clicking Accept!! -- bill ...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

Cell Formula reference to cell Based On third Cell Content #2
I want to build a formula in one cell that calls a second based on the numeric value in a third. Specifically, Say the frst cell is F10. I want it to look up the number in F1, and then if F1=1, F10=E9*something if F1=2, F10=D9*something if F1=3, F10=C9* something etc. Hi! I notice that you use "etc" which means there are more. Exactly how many more? That number will determine the best way to approach this. Biff >-----Original Message----- >I want to build a formula in one cell that calls a second based on the >numeric value in a third. > >Specifically, Say...

Open Report Based on Query
I have a report based on a Query. In the Query's criteria On one field I have this =Forms!frmCheckAction!TxtSalesID How do I open this report based on this query from a different form? Do I leave the Query criteria blank? I'm getting confused! Thanks DS Change the way you open the report so you can open it from different forms Use the WhereCriteria of the open report command line, living the RecordSource without a criteria Dim MyCriteria As Text MyCriteria = "[FieldName] = " & Forms!frmCheckAction!TxtSalesID Docmd.OpenReport "ReportName",,,MyCriteria ...

Calling employee lookup from button through VBA code
Dear All, Can anyone show me how to call an existing GP employee lookup from a button of a modified form through VBA code. Thanks in advance. -- Developer Hi, If I'm understanding the question - you need to add the lookup button to your project and make sure your project provides that it runs on the modified form. Leslie "Dexdev" wrote: > Dear All, > > Can anyone show me how to call an existing GP employee lookup from a button > of a modified form through VBA code. > > > Thanks in advance. > > -- > Developer Hello Dexdev As per...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

Limiting a column to certain values
I have a table that contains PLSS information and want to restrict the columns to certain values. Since there is a pattern in what they are restricted to, I wonder if there would be an easier way than to create a lookup table and use a constraint. For instance, my values for one field is limited to 3 characters: from 01-49, with the third character always an 'E' or 'W' Would this be easier done in a query or stored procedure or function than to create a lookup table? Thanks for your help. In the case you describe you can use a CHECK constraint: CR...

Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information upon lookup; other user sees the vendor list and needs to clik on the show details - how do you get the show details window to be the default option you see. Thansk! Check for full stops/periods/dots on the window title bar before or after the window name. It is possible to use VBA or modifier to open the details automatically. David Musgrave [MSFT] Escalation Engineer - Microsoft Dynamics GP Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (formerly Microsoft Business Solutions) http://www...

Pasting Values #2
Hi, I have developed a quoting tool which creates an output sheet detailing all the info, What I would like to be able to do is take a copy of this and paste the values (and Format to a new workbook). I'm currently doing this manually and I unsure how to automate it! I have never used VB which I'm guessing is the only way of doing it, so please be gentle!! Thanks, Sam. -- sammy2x ------------------------------------------------------------------------ sammy2x's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29674 View this thread: http://www.excelf...

Lookup #4
I am trying to label my bank info that I import into Excel. I have a column with the charge amount and a column with the recipient. I think a lookup table would work for this, but I need to have it check for wildcards. For example, the grocery chain is numbered here, so one might be GROCER 5454 and one may be GROCER 8724, so I need the function to look for GROCER*, right? Also, if it does not fit any category, how can I get it to use "Other"? Do I make that one just "*"? Mine is not working very well. Maybe my syntax is wrong. Any ideas? TIA Is there anyway you can st...

How do you stop excel from charting empty cells/null values as zer
I'm trying to create several charts from my excel data. Several of the chart's reference cell (established range) contains formuals. When the cell's formula returns a null value, chart display's as zero. How do I setup chart to display null as nothing versus zero? The workaround for charting calculated cells when you may have missing data is to use an If formula that selects NA() if there is missing data. Something like -- = If( d2 = "", NA(), b2*d2) You'll need to replace your actual cell references and formula. This formula will place a #N/A in tho...

How to Copy the value of a cell to any given cell
How to copy the Value of a cell just by clicking on that cell and having its value pop on another cell. For example: A1=15, upon clicking on this cell, i would like the value to post on cell b1. What I am doing is displaying a monthly calendar, to the right of the last day of the week I have a blank cell where I currently type which day of the week someone received a paycheck and then next to this cell I enter the value of the paycheck. It would be nice just to click on the date and have it copy to the blank cell. I tried creating command button overlayed on top of the cell with the va...

Update cell based on date range
Hey guys! I posted this in the General Forum, but I figured this woul be a better place for this question. I was wondering if I could ge some help here. I would like to update a cell based on a date range For example, I would like to update the value of a cell to the value o another cell if the current date is between July 1st and July 10th However, if the date is outside the date range, I want the value fo that cell to not be updated, and be the previous value. Can anyone giv me an example as to how I would do this? Thanks!! -- deversol -----------------------------------------------------...

Filter message by reverse domain lookup
Hello all, I am kinda new to Exchange and was wondering if there was a function built into Exchange 2000 that would "look" at the source domain of the email message and then perform a "reverse" DNS lookup to verify that the domain actually exists and if it doesn't then have Exchange discard the email without sending an NDR? Is this possible strait out of the box? It seems like I remember iMail gateway having this feature. I am trying to filter out some of the spam that fills up my users inboxes. Thanks in advance. Jeremy jsteger@bellsouth.net (Jeremy Steger) wrote...

Can you change the default lookup from Account to Contact?
Is it possible to set the lookup for a customer field to default on "contacts" rather then "accounts?" We do more business with contacts so it is a pain to have to change this all the time. there is no supported way to do this in the current release -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Martin Flaherty" <mflaherty@techpg.com> wrote in message news:ucdNM74uEHA.3416@TK2MSFTNGP09.phx.gbl... > Is it possible to set the lookup for a customer field to default on > "contac...

a good add-in to handle matrix items?
I am looking for a good add-in to better handle matrix items. I need better handling of matrix items on POs. Also, I need something that will make adding items to matrices much easier than the stock RMS way. I have looked at the Retail Realm solution, but was wondering if there was anything else out there that I should check out. I setup a system for retail shoes stores where every item is part of a matrix and I can confirm that our buyer has been really happy with the retail realm add on. It even prints PO's with matrix view. Dave Weston Sole Desire Stringers wrote: > I am looking...