INDEX and MATCH 12-08-09

Hi,
Excel 2003:
I have a column of figures in ascending order (can't be reveresed)

I'm trying to use INDEX and MATCH to find a value that is MORE THAN or equal 
to a lookup_value. (and then to pick an adjacent cell value)


MATCH works great to recover the LESS THAN or equal value.

Is there another formula that I should be using?
TIA

Phil 


0
PPL
12/8/2009 1:10:33 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1068 Views

Similar Articles

[PageSpeed] 56

Try this array formula** :

D2 = lookup value

=IF(D2>MAX(A1:A10),"out of range",INDEX(B1:B10,MATCH(TRUE,A1:A10>=D2,0)))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

-- 
Biff
Microsoft Excel MVP


"PPL" <pp1@shawRemoveThis.ca> wrote in message 
news:kIhTm.52718$X01.49927@newsfe07.iad...
> Hi,
> Excel 2003:
> I have a column of figures in ascending order (can't be reveresed)
>
> I'm trying to use INDEX and MATCH to find a value that is MORE THAN or 
> equal to a lookup_value. (and then to pick an adjacent cell value)
>
>
> MATCH works great to recover the LESS THAN or equal value.
>
> Is there another formula that I should be using?
> TIA
>
> Phil
>
> 


0
T
12/8/2009 3:34:50 AM
Hi Biff,

Thanks for getting back to me. Sorry it's taken me so long to reply.

Undoubtedly I should have given more info. However, I found a solution that 
met my needs.

I used a combination of OFFSET, INDEX and MATCH

The problem I had was that when using INDEX and MATCH alone the result 
always returned the LESS THAN or equal value.

I used OFFSET to pick the result from the cell in the same column but next 
row as follows:

Sheet2 contains names of people on-call & dates

Column C = List of names

Column D = List of dates

Using index & match alone allows me to easily see who is currently on call 
(current date is entered in C2 in the current sheet) (i.e. Sheet1) but it 
doesn't allow me to see who is the next person in the list and when they 
start their duty. The following works for me.



=OFFSET(INDEX(Sheet2!$C$6:$C$100,MATCH($C$2,Sheet2!$D$6:$D$100),1),1,0)

Thanks again.

I hope this helps someone else

Phil



"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:<e4$2ld7dKHA.4952@TK2MSFTNGP06.phx.gbl>...

> Try this array formula** :

>

> D2 = lookup value

>

> =IF(D2>MAX(A1:A10),"out of range",INDEX(B1:B10,MATCH(TRUE,A1:A10>=D2,0)))

>

> ** array formulas need to be entered using the key combination of

> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
> SHIFT

> key then hit ENTER.

>

> -- 

> Biff

> Microsoft Excel MVP

>

>

> "PPL" <pp1@shawRemoveThis.ca> wrote in message

> news:kIhTm.52718$X01.49927@newsfe07.iad...

> > Hi,

> > Excel 2003:

> > I have a column of figures in ascending order (can't be reveresed)

> >

> > I'm trying to use INDEX and MATCH to find a value that is MORE THAN or

> > equal to a lookup_value. (and then to pick an adjacent cell value)

> >

> >

> > MATCH works great to recover the LESS THAN or equal value.

> >

> > Is there another formula that I should be using?

> > TIA

> >

> > Phil

> >

> >

>


0
PPL
12/11/2009 12:47:43 PM
Good deal. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"PPL" <pp1@shawRemoveThis.ca> wrote in message 
news:QbrUm.6772$ha3.3822@newsfe19.iad...
> Hi Biff,
>
> Thanks for getting back to me. Sorry it's taken me so long to reply.
>
> Undoubtedly I should have given more info. However, I found a solution 
> that met my needs.
>
> I used a combination of OFFSET, INDEX and MATCH
>
> The problem I had was that when using INDEX and MATCH alone the result 
> always returned the LESS THAN or equal value.
>
> I used OFFSET to pick the result from the cell in the same column but next 
> row as follows:
>
> Sheet2 contains names of people on-call & dates
>
> Column C = List of names
>
> Column D = List of dates
>
> Using index & match alone allows me to easily see who is currently on call 
> (current date is entered in C2 in the current sheet) (i.e. Sheet1) but it 
> doesn't allow me to see who is the next person in the list and when they 
> start their duty. The following works for me.
>
>
>
> =OFFSET(INDEX(Sheet2!$C$6:$C$100,MATCH($C$2,Sheet2!$D$6:$D$100),1),1,0)
>
> Thanks again.
>
> I hope this helps someone else
>
> Phil
>
>
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message 
> news:<e4$2ld7dKHA.4952@TK2MSFTNGP06.phx.gbl>...
>
>> Try this array formula** :
>
>>
>
>> D2 = lookup value
>
>>
>
>> =IF(D2>MAX(A1:A10),"out of range",INDEX(B1:B10,MATCH(TRUE,A1:A10>=D2,0)))
>
>>
>
>> ** array formulas need to be entered using the key combination of
>
>> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
>> SHIFT
>
>> key then hit ENTER.
>
>>
>
>> -- 
>
>> Biff
>
>> Microsoft Excel MVP
>
>>
>
>>
>
>> "PPL" <pp1@shawRemoveThis.ca> wrote in message
>
>> news:kIhTm.52718$X01.49927@newsfe07.iad...
>
>> > Hi,
>
>> > Excel 2003:
>
>> > I have a column of figures in ascending order (can't be reveresed)
>
>> >
>
>> > I'm trying to use INDEX and MATCH to find a value that is MORE THAN or
>
>> > equal to a lookup_value. (and then to pick an adjacent cell value)
>
>> >
>
>> >
>
>> > MATCH works great to recover the LESS THAN or equal value.
>
>> >
>
>> > Is there another formula that I should be using?
>
>> > TIA
>
>> >
>
>> > Phil
>
>> >
>
>> >
>
>>
>
> 


0
T
12/11/2009 4:40:45 PM
Reply:

Similar Artilces:

how to input a 12 digit number into a cell
Hi i am pretty new to excel but here goes. The problem I am having is when I try to input a 12 digit number into an excel cell i get *1.03001E+11 *instead of 1*03000568221* 10 digits seem to go in ok. I copy and paste this 12 digit number. any ideas what this can be thanks in advance. -- jedi ------------------------------------------------------------------------ jedi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35538 View this thread: http://www.excelforum.com/showthread.php?threadid=553009 jedi Wrote: > 1.03001E+11 instead of 1*03000568221* 11 dig...

Report based on Crosstab Query 02-12-10
Each year for the past 4 to 5 years, I have used a small db (I created) to supplement our accounting systems lack to provide an actual report for each and every fund budget we have. The detail table in the db I created only has 3 columns: Fund, Acct, and Amount. I created a crosstab query to list: CatDesc, Acct, Importance, Name, and each fund as columns and the amount. CatDesc Acct Imp Name 300 310 320 340 This portion works great except for the fact when I want to create the final draft of the budget. I manually arrange the fund columns into t...

Update Table 12-03-07
Dear All I need your help to make an update querrie. I have the following tables: 1-Date Table : only 1 field dates 2- Customers Code: 2 fields (Code - Name) 3- Customers balances : Code - Name - Date and Balance. I need to make an update querrie that seach date by date (From table 1) for each customer (from table 2) if the customer exist for that date in the table 3 and if don't exist add a row for that customer : Code - Name - Date and balance = 0 Thank you for your help. That is not an update but an insert query. Untested, but should be something like: SELECT x.code, x.nam...

M2006 transaction match problem
Hi, Every few weeks, my money file gets into a strange state where the only transactions that are presented to be matched are in the future (today's date or greater). The kicker is that no matches actually can be performed performed - the "Done" button that performs it doesn't work. After I repair the file, the problem goes away for another few weeks. I have been unable to predict when this will happen. Anyone else experience this? Is this a known problem. -- John Yes! I have been running Money 06 for a few weeks now. A couple of days ago I tried matching ...

How to stop automatic "matching"
Everytime I download transactions from my bank's web site, Money 2002 tries to match some of them with older transactions. I have to manually unmatch them and it's a pain. Many times the transaction Money wants to match with is not even for the exact same amount! Does anyone know how to stop this? Thanks, Mika Mantere I don't know how to stop automatic matching but you can set the range MS Money looks back to 1 day. Tools->Options->Online Services. Set the "Match Transactions within a 'fill in the blank' day range" You can't enter zero but you ...

Importing messages from OE to WLM 08-19-10
I followed the instructions to import messages from an old computer running Outlook Express to WLM on my new computer running OS Windows 7. I used the import button in WLM and it seems to have been successful. A file in Storage Folders called "imported folder" was created. However I clicked on this file and it showed zero files. I went to c:\users\owner\AppData\local\microsoft\ wlm and I found all the files I transferred. My question is how do I read these files using wlm or some other programme. Thanks for your support. What instructions did you follow? Did you include...

problem with Non User Queue ( did not match any records)
background mail enters public folder via email (help@domain.com or Support@domain.com mail gets forwarded via exchange to a user/mailbox called helpdeskservice (helpdeskservice@domain.com) (this needs to be here because other programs use the public folder as well) CRM-Router picks up helpdeskservice@domain.com into queue call helpdeskservice (with email of helpdeskservice@domain.com) via POP3 (this is because the EXCHANGE CRM-Router is broke for winows2008 Exchange 2007 64bit installs) problem here that I get in the logs is when I test from an "outside email" #35241 - The r...

Help with Formula #12
I am new to Excel and am trying to make a formula for items I am selling. After 2 hours of trying I am asking for help. I need starting entries in E5 which has a price to then subtract G5,H5,I5,J5,K5 and have total in K5. I would like this formula to work for 100 rows. I would also like to have the dollar sign show and two decimals show. Column B5,C5,D5 have text. If someone would email me the correct formula and tell me where to paste it I would greatly appreciate it. Thank you. In cell K5, enter: =E5-SUM(G5:K5) and copy down -- Gary''s Student - gsnu200825 "one...

Help does not allow entry in Answer Wizard or Index tab #2
Hello -- I am using Office 2000 Pro; Excel version is 9.0.6976 SP-3.. If I click a word in VBA, Help displays a help screen about it. However, the top menu bar says "Microsoft Access Help". I have Access on the machine. And, if I click the Answer Wizard or Index tab, the shape of the cursor in any field changes to a horizontal, double-ended arrow. Can anyone tell he how to solve this problem? Thanks for any help. L Mehl --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.572 / Virus Database: 362 - Release Da...

Building Full text index on Exchange 2003
Hello all, I am trying to build a full text index of the mailstore. The catalog is build fine but when performing a full population of the mailstore no mailboxes are indexed. The following messages show up in the Event log ------------------------------------------- Event Type: Information Event Source: Microsoft Search Event Category: Gatherer Event ID: 3018 Date: 28-7-2006 Time: 8:35:05 User: N/A Computer: TBG-VMS Description: The end of crawl for project <ExchangeServer_TBG-VMS privD88B396F> has been detected. The Gatherer successfully processed 1 documents totaling 0K. It failed...

Contacts 08-14-03
Question- As a test, I've created a text file in notepad containing the following 2 records: "Cobb, Chris" "Halstead, Rebecca" The delimeter is Comma, The text qualifier is double-quote The contacts import without error. When I activate the contacts, I don't see the names. Any idea why? Seems as though should without issue. Thanks, Chris ...

Email taking 12 min to move from an Exch 5.5 srvr to an E2K3 BE sr
We have an FE Exchange 5.5 server and a BE Exchange 2003 server sitting on the same LAN, on a 10/100 MB backbone. I have noticed that emails for inbound delivery are staying in the Internet Mail Connector queue for about 12 minutes before they are delivered to the BE server. I don't have a clue as to why this is happenning. Can someone point me in the right direction, on what I need to look at figure out and resolve the delay. Thank you. ...

Customize user form 10-12-04
Hi there, is it possible to customize the user form? Thanks in advance! ------=_NextPart_0001_3B4FD825 Content-Type: text/plain Content-Transfer-Encoding: 7bit It is not possible to customize the User form in CRM version 1.0 or 1.2. Thank you, Tami J. Lemar Microsoft Business Solutions Support This posting is provided �AS IS� with no warranties, and confers no rights. ------=_NextPart_0001_3B4FD825 Content-Type: text/x-rtf Content-Transfer-Encoding: 7bit {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}} \viewkind4\uc1\pard\f0\fs20 It is not pos...

Post Callout 08-11-04
Hi, I wanted to check out if Post Callout would work for the user entity. Please do shed some light on this issue as need it urgently. Regards Tia How are the problem? Test your PostCallout component on PostUpdate. (Is more simple who PostCreate for User because you cant delete User from CRM). If work for PostUpdate, work for PostCreate. Hope this helps. []'s Vin�cius Pitta Lima de Ara�jo <anonymous@discussions.microsoft.com> escreveu na mensagem news:44c001c47fb6$e01a1e40$a401280a@phx.gbl... > Hi, > > I wanted to check out if Post Callout would work for the > use...

frontpage 2003 12-16-09
Hi before i updated to service pack 3 frontpage worked fine. I used to find the home html open site make the adjustments i needed save it and the update was down loaded. Now the site states it is protected by user name and password but does not grant me access to put in the user name and password. What could be the problem. I tried to remove service pack 3 which caused all kinds of problems so i re-installed service pack 3. But still frontpage will not grant me access to the login box. I have access the site through file zilla but cant seem to make the updates or find the right p...

Error message 12-18-09
Hi All, I have the below error message sometime when I either trying to enter some record or search for some record. I am sure no one is using the same database at the same time. can anyone advice me one this? “The Microsoft office access database engine stopped the process because you and another user are attempting to change the same data at the same time.” If you have a form open to edit a customer's name and address and then at the same time you open another form with the customer's name and address where you can also edit the name and address - access will giv...

Error 09-21-04
I'm getting the following Error in the System Log on my Domain Controller. This account referenced is my CRM Server Account. Event ID 11 Source KDC There are multiple accounts with name host/MY_CRM_SERVER.MYDOMAIN.pri of type DS_SERVICE_PRINCIPAL_NAME. I know there is a kbase article about this. You might want to contact support as there is a fairly specific procedure needed to remove the duplicate service principal name that you are encountering. I thought I had the number, but I can't find the article link Matt Parks MVP - Microsoft CRM -----------------------------------...

Issues with 12.1.2 with tables and page breaks.
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I work for a small company that is primarily mac based. I noticed in this latest patch that our documents are having major issues after this patch. For one the text in tables is missing totally or partially. It is there though. The solution I found is to select the table and do a word wrap in the table properties but we have thousands of documents like this. Also I have been getting reports and also noticed that it is inserting extra page breaks in documents. Not sure what is going on with this new patch but it has made...

how to tell is 12.2.1 is installed?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel hello, how can I tell if 12.2.1 is install. After running the installer is appears all my office apps are show 12.2.0 as their version in the Finder, but show 12.2.1 in the About menus. I need to automate installation of 12.2.1 across many Macs, so how can I tell is 12.2.1 is installed without actually opening Word and looking at the about box? It seems the CFBundleGetInfoString string, the CFBundleVersion string, and the CFBundleShortVersionString are not being updated. Thanks, Allan Marcus Los Alamos National Lab ...

Locate and Match Data
Thanks up front to anyone who can help me. My problem is that I hav two columns of data, columns A and B. Each column has a given set o numbers in it, approximately 3000 rows deep. I need to run a progra that tells me what numbers in column B match up to those in column A. The matches should be automatically listed in column C. Thanks! :cool -- kukarooz ----------------------------------------------------------------------- kukarooza's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=779 View this thread: http://www.excelforum.com/showthread.php?threadid=2728...

Two combo box for a report 09-10-07
Hello, A blessed day to you all! I have another question regarding reports. Let me try to explain this as far as i can..Please bear with me.. What i have done so far.. I have a simple report to display Issue Vouchers for a Certain Site. I have created an unbound form with a Combo Box (List of Site i have created base on the Site table) which will ask the user what Site only to be displayed in the report, so basically the user is trying to filter. So when the user choose a site example, Head Office and press the Preview button then the report will filter and display the report with only th...

vlookup or match formula help
i am trying to match, or line up identical ID#'s in wksheet 1 - column A (which has 305 lines/rows) to ID#'s in wksheet 2 - column A (which has 9500 lines/rows). (also, each wksheet has a column B with misc $ amts). thanks In Sheet1, In C2, copied down: =VLOOKUP(A2,Sheet2!A:B,2,0) will return the amounts from Sheet2's col B -- Max Singapore http://savefile.com/projects/236895 Downloads:17,400 Files:358 Subscribers:55 xdemechanik --- "Will123" wrote: > i am trying to match, or line up identical ID#'s in wksheet 1 - column A > (which has 305 lines/rows) t...

Timeline: where it will match up data automatically
I'm trying to create a timeline for a screenplay I'm writing. I found a nifty little template: http://office.microsoft.com/en-us/templates/TC010162661033.aspx But what I don't like is that I have to manually enter and move around the events, and enter the years manually, etc.. I need to set up 3 different timelines: 1. for 1 year, where each line on the timeline is a month 2. for 12 years, where each line is a year 3. a 60 year period, where every 2 lines is a decade, ie. from 1930's to 1990's I want to make a chart where I enter the date and the event, then excel...

Finding/creating record in one form to match previous form
I'm constructing a set of data entry forms. The user will fill these in sequentially with data on different assessments of a single individual. Each form has 'Participant ID' as the primary key. When the user tabs out of the last field in e.g. Form A, I have the next form in sequence opening up e.g. Form B. What I'd appreciate some advice on is how to have Form B search for a record with ParticipantID matching the ParticipantID of the record which was just open in Form A, and either display that record, or if it does not exist then to create it (i.e. fill the Part...

CRM SQL Error 09-20-07
Should an error like this show up in the event log, does it show up anywhere else? I can't find this error any place on the server and can't find anything around the internet about it. I was able to find out some info on WRPC, so I am pretty sure that this has to do with the screen security timing out. I am not asking to have this fixed, just trying to figure out how I can get more information on an error when it happens. SQL Page error: http://img.photobucket.com/albums/v258/Jeembo/SQLError.png Thanks, Jim Look at the last question on this page: http://www.microsoft.com/dynamic...