vlookup and match first value greater than

I need to lookup a product id and return the date of the first value greater 
than zero
For instance

    a             b     c      d      e    f      g
1 Date        4/12 4/19 4/26 5/3 5/10 5/17
2 Product A  0     11     35     0      0    125
3 Product B  35    50     75    100   25   36

If I lookup Product A in the spreadsheet and want to get results from 
columns e through g I would want to see 5/17 as the result.

I have this formula which works against a fixed row reference but have been 
unable to incorporate a lookup value into this to return the same result.
INDEX($e$1:$g$1,MATCH(TRUE,INDEX($e:$g2>0,0),0))

How do I add to this formula so that I can type a Product ID into a cell and 
lookup and return this data from the table? ie. lookup Product A and show the 
first date greater than 0.
1
Utf
4/12/2010 8:14:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
4387 Views

Similar Articles

[PageSpeed] 3

One way
Assuming input in say, A7 for the product, eg: Product A
put this in B7, normal ENTER to confirm will do:  
=INDEX(OFFSET($E$1:$G$1,MATCH(A7,A2:A3,0),),MATCH(TRUE,INDEX(OFFSET($E$1:$G$1,MATCH(A7,A2:A3,0),)>0,),0))
Success? hit YES below
-- 
Max
Singapore
--- 
"MPI Planner" wrote:
> I need to lookup a product id and return the date of the first value greater 
> than zero
> For instance
> 
>     a             b     c      d      e    f      g
> 1 Date        4/12 4/19 4/26 5/3 5/10 5/17
> 2 Product A  0     11     35     0      0    125
> 3 Product B  35    50     75    100   25   36
> 
> If I lookup Product A in the spreadsheet and want to get results from 
> columns e through g I would want to see 5/17 as the result.
> 
> I have this formula which works against a fixed row reference but have been 
> unable to incorporate a lookup value into this to return the same result.
> INDEX($e$1:$g$1,MATCH(TRUE,INDEX($e:$g2>0,0),0))
> 
> How do I add to this formula so that I can type a Product ID into a cell and 
> lookup and return this data from the table? ie. lookup Product A and show the 
> first date greater than 0.
-1
Utf
4/12/2010 11:05:01 PM
Hi,

I do not see the problem with your formula.  Your formula yields the answer 
as 5/17.  The only correction you have to make is that the INDEX function 
should be ,
INDEX($e2:$g2>0,0).

Also, why is the range E2:G2 - why is not B2:G2

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP

"MPI Planner" <MPIPlanner@discussions.microsoft.com> wrote in message 
news:61FC01C9-3D4F-41EC-84FE-460FBB8341CF@microsoft.com...
> I need to lookup a product id and return the date of the first value 
> greater
> than zero
> For instance
>
>    a             b     c      d      e    f      g
> 1 Date        4/12 4/19 4/26 5/3 5/10 5/17
> 2 Product A  0     11     35     0      0    125
> 3 Product B  35    50     75    100   25   36
>
> If I lookup Product A in the spreadsheet and want to get results from
> columns e through g I would want to see 5/17 as the result.
>
> I have this formula which works against a fixed row reference but have 
> been
> unable to incorporate a lookup value into this to return the same result.
> INDEX($e$1:$g$1,MATCH(TRUE,INDEX($e:$g2>0,0),0))
>
> How do I add to this formula so that I can type a Product ID into a cell 
> and
> lookup and return this data from the table? ie. lookup Product A and show 
> the
> first date greater than 0. 

0
Ashish
4/13/2010 12:24:46 AM
Think the earlier can be simplified to just:
=INDEX($E$1:$G$1,MATCH(TRUE,INDEX(OFFSET($E$1:$G$1,MATCH(A7,$A$2:$A$3,0),)>0,),0))
where A7 = input for the product, as before 
-- 
Max
Singapore
--- 

0
Utf
4/13/2010 2:30:01 AM
Assuming your data in A1:G3
Criteria in A7

=MAX(INDEX((A2:A3=A7)*(E2:G3>0)*E2:G3,))

Normally enter


"MPI Planner" wrote:

> I need to lookup a product id and return the date of the first value greater 
> than zero
> For instance
> 
>     a             b     c      d      e    f      g
> 1 Date        4/12 4/19 4/26 5/3 5/10 5/17
> 2 Product A  0     11     35     0      0    125
> 3 Product B  35    50     75    100   25   36
> 
> If I lookup Product A in the spreadsheet and want to get results from 
> columns e through g I would want to see 5/17 as the result.
> 
> I have this formula which works against a fixed row reference but have been 
> unable to incorporate a lookup value into this to return the same result.
> INDEX($e$1:$g$1,MATCH(TRUE,INDEX($e:$g2>0,0),0))
> 
> How do I add to this formula so that I can type a Product ID into a cell and 
> lookup and return this data from the table? ie. lookup Product A and show the 
> first date greater than 0.
0
Utf
4/13/2010 2:31:01 AM
Reply:

Similar Artilces:

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

Multiple Search Criteria/ Index Match
I am using the following formula to retrieve data that matches tw specific criteria. =INDEX(C2:C80,MATCH(1,(A2:A80="Liverpool")*(B2:B80="January"),0)) using ctrl,shift, enter to give curly brackets to make it work. This formula is then repeated in the cell directly below, but searche for "February", below that "March" and so on. However when I copy my formula, I have to change the month for eac cell and therefore have to put in the curly brackets again. Is there anyway around this -- Message posted from http://www.ExcelForum.com Have a list with the ...

Matching records based on multiple columns
I need to compare records from two different sets of data. Set 1 is a master list and Set 2 is a sublist of the master. The comparison involves three columns from each set; Tag#, Location, S/N. I want to be able to match records from Set 2 to Set 1, ending with an additional column that shows if there is a match (true/false). What is the most efficient way of completing this task? Thanks concatenate a column that contains the 3 required fields on both tabs, then do a vlookup. -- -John http://www.jmbundy.blogspot.com/ Please rate when your question is answered to help us and ot...

how to plot ONE x-y curve of two datas with different x values?
I have this data: x: (1.1) (3.6) (6.0) (7.5) (9.2) ... y: (4.2) (5.6) (6.3) (9.4) (9.6) ... and this data: x: (2.3) (3.2) (5.4) (6.7) (7.2) ... y: (1.2) (2.4) (3.3) (6.4) (8.9) ... Now I want a x-y chart with a SINGLE smooth curve, showing the average result of these two datas. By average, I mean that, suppose I have two curves, I want a curve which is always in the middle of them. As you can see, my problem is that the x data is not very regular, and the x data of both datas usually do not coincide. Thanks! You need to create a single table of all your x values, and interpolate to find th...

import only the first part of a text file
Hello -- An external application generates a text file which looks like the text between the lines: ----------------------------- ARRAY 1 6 #X-values 30.201515 30.260118 30.330780 33.360526 33.430092 34.737403 < --- blank line #Y-values 1.860447 0.058603 0.070662 3.029746 0.069566 0.870192 ----------------------------- Our charting process imports the text file, but needs only the data above the blank line. It is possible that the top part of the data could contain as many as 65,500, so I can's simply import the whole file i...

change value with report
I have a report that has worked fine for years on my system. The query is based on one yes/no check mark on about 10 records every quarter but now it has 50 records. To much to change by hand. After I run the report I need to set this field to false and set a second field to true. Can someone tell me were to look to find a solution Thank You Stephen Use the report's Close event to run an update query that will reset those fields' values for you. -- Ken Snell <MS ACCESS MVP> "Stephen" <smile_inspector@hotmail.com> wrote in message news:uwj1zWhiIH...

#VALUE! #2
Can anyone answer this: When i paste in info from a web site, one of the columns contains numbers, yet when I try to refer to those numbers in formulas in other cells, it always comes up with the #VALUE! message, as though it were text in there, not numbers. Any ideas? Please refrain from multiposting, for a possible answer see your post in the worksheet.functions NG -- Regards, Peo Sjoblom "Cam" <kingsandmadmen@hotmail.com> wrote in message news:2c43601c39384$b28da7e0$a601280a@phx.gbl... > Can anyone answer this: When i paste in info from a web > site, one of...

Mouse Move response based on combo box value
Hello, all. I have been asked to add a feature to an exiting form where a "note" will appear on mouse over only if a combo box of a continuous form is a particular value (i.e. Annual Safety Training). The main form is frmEmployees, the subform is subformClassesAttended. The combo box is Combo12, but it is displaying the text description (colmun 2) associated with a bound field of a number type, classID. I have a hidden label, lblInfo, with the info to be displayed but am not sure of the coding & whether I should be referencing the description in Combo12 or Class...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

how do I reveerse name order last name, first name to first name, last name
I have a long database, 27k with names that I want to mail to. The list is last name, first name. I want to do a global change to first name, last name. I also want to add "Mr" to it. For example: the list now is Smith, John. I want to change it to Mr. John Smith for the entire list. Assuming there is only one comma in each: ="Mr. "&TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))&" "&LEFT(A1,FIND(",",A1)-1) Note that some of the women might be offended.... 27k names, all male? Scott Don Smith wrote: > I hav...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

Vendor Name on the inquiry screen should match the V Card field
In the vendor lookup screen, the “vendor name field” is too short. This fields needs to be lengthened on the screen (perhaps the “additional sort field” should be shortened). ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/C...

Options reset to values of most recently opened workbook
I've noticed a behavior in Excel 2000 SP-2 on Windows 2000 Professional SP-3 and I'm not sure if it is an error, by design, or correctable by some option or setting I'm missing When I open multiple workbooks I've found that the options settings under Tools/Options for the most recently opened workbook apply to ALL open workbooks. It might be best to explain by a couple of examples 1. I normally have the "Windows in Taskbar" option checked so I can view each of my open workbooks as a separate item in the taskbar. I have all of my workbooks set up this way. If I r...

Trend Line constants as Excel Cell Values
I want to put the constants of a 2 degree polynomial trend line into Excel Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken Hi Ken, Supposing your x-range is in A2:A10, and y-range in B2:B10, select a 3-column x 1-row area and enter the following formula and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B10,A2:A10^{0,1,2},0,) Regards, B. R. Ramachandran "Ken" wrote: > I want to put the constants of a 2 degree polynomial trend line into Excel &g...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Scatter Chart with non-numeric values on X axis
Hi, I'm trying to figure out how to create a scatter chart (graph) wit words for the x axis. I'm trying to setup a graph showing availabilit of servers, so for the Y axis I want to show a percentage (betwee 1-100%), and for the X axis, I want to show the name of the servic (mail, news, etc). However, it seems that with a scatter chart I ca only do numeric values on both axis. Is there any way around this? Thx -- penas ----------------------------------------------------------------------- penasm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View...

Index Match Functions #2
Has anyone ever combined the Index and Match functions to do lookups? oh yeah! http://www.cpearson.com/excel/lookups.htm UnumProvident User wrote: > Has anyone ever combined the Index and Match functions to do lookups? > > ...

VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

Making a worksheet and a graph that doesn't show zero values
Hi I am trying to build a template worksheet for my research. I get a file of logged data per day. I want to be able to copy the logged data from a .CSV file to a template file. Then separate worksheets will look at the raw data and import data from certain columns into that worksheet. The data is not logged in a regular way, there can be 30 cells between logging intervals or 29 or 31 cells. I can select that every cell in a column of Worksheet 1 looks at a set cell in the "Raw Data" worksheet. This will then give me a column consisting of logged data values with zeroes ...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

Can not see CString's value when debugging
I'm very sure I'm in the CString's life scope, but I can not see its value but only some "???". Any other type of variables are ok. But some days ago, in the same project, I can see CString's value when debugging. So, what's wrong of CString in debug mode? Now I nearly can't do the debugging job! >So, what's wrong of CString in debug mode? It's a limitation of the debugger. Newer versions of CString have optimisations for the storage of small/long strings that the debugger doesn't cater for :( Dave ...

Matching call data based on date, time and number called to give c
I have the following two tables on different sheets, both containing other information within them that is irrelevant in my question. SHEET 1 Date - Time - Number Called Duration Cost 01/02/10 09:05:21 01234123456 02:16 0.50 SHEET 2 Date - Time Number Called - Duration Extension 01/02/10 09:07:56 01234123456 02:15 1234 01/02/10 15:30:45 01234123456 01:59 1234 Sheet 2 HAS A LOT MORE RECORDS THAN SHEET 1 AND THERE ARE A NUMBER OF CALLS TO THE SAME NUM...

Q276597 doesnt work (Turn Off E-mail Matching for Certificates)
I tried the registry hack found at : http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;276597&Product=3D= out however, it doesnt work under WinNT + Outlook2000 nor under WinXP + Outlook2002. I always get promted with "could not encrypt for the selected recipients" also i selected my encryption cert manually as default cert. I also tried a certificate which had no email address - that didnt work either. There is something wrong about Q276597 - anyone figured out how that should work ? Thanks, Regards Ren=E9 ...

Turn cell red if today is greater or equal to date in cell
Hi, I'm storing dates in cells in a column that represent future deadline dates. If today's date is greater or equal to the date stored in the cell, I want to turn that same cell red and text bold. Or, at least somehow flag it. I've been using conditional formatting but have not been able to get exactly what I need. Thank-you, Rick onlineXmanX@exciteX.com (remove X's for true email) Hi - goto the conditional format dialog after selecting your cell (lets say you have selected B1) - choose 'Formula is' - enter the following formula: =B1<=TODAY() - c...