Reference function from an out of sort table

I'm looking for a reference function to pull the result from an out of sort 
table (see table example below). The result should always come from the 
reference value LT A14.  The table is always a whole number 0,1000,2000,3000, 
etc.

I've tried the following functions, but they all require data in ascending 
order and/or exact match of the value in A14.
=VLOOKUP($A$14,A$2:B$10,TRUE)
=INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
=LOOKUP($A$14,A$2:A$10,B$2:B$10)

Table example:
	A	B
2	1000	0.525
3	6000	0.946
4	3000	0.675
5	0	0.457
6	4000	0.746
7	2000	0.606
8	8000	1.29
9	5000	0.835
10	7000	1.09

If A14 = 3125, then result in B14 is 0.675.

I don't want to have to use a complex IF/AND, like:
=IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue 
IF/AND")
 
Thanks, Kevin
0
Utf
3/1/2010 6:01:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
715 Views

Similar Articles

[PageSpeed] 23

On Mon, 1 Mar 2010 10:01:01 -0800, AFSSkier
<AFSSkier@discussions.microsoft.com> wrote:

>I'm looking for a reference function to pull the result from an out of sort 
>table (see table example below). The result should always come from the 
>reference value LT A14.  The table is always a whole number 0,1000,2000,3000, 
>etc.
>
>I've tried the following functions, but they all require data in ascending 
>order and/or exact match of the value in A14.
>=VLOOKUP($A$14,A$2:B$10,TRUE)
>=INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
>=LOOKUP($A$14,A$2:A$10,B$2:B$10)
>
>Table example:
>	A	B
>2	1000	0.525
>3	6000	0.946
>4	3000	0.675
>5	0	0.457
>6	4000	0.746
>7	2000	0.606
>8	8000	1.29
>9	5000	0.835
>10	7000	1.09
>
>If A14 = 3125, then result in B14 is 0.675.
>
>I don't want to have to use a complex IF/AND, like:
>=IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue 
>IF/AND")
> 
>Thanks, Kevin


Try this modifying your second formula like this

>=INDEX(A$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,1),2)

or, a little simpler, just

=INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0))

Hope this helps / Lars-�ke
0
Lars
3/1/2010 6:15:14 PM
>If A14 = 3125, then result in B14 is 0.675.

What is the "rule" for finding the correct result? Is it: the closest value 
that is less than the lookup value?

-- 
Biff
Microsoft Excel MVP


"AFSSkier" <AFSSkier@discussions.microsoft.com> wrote in message 
news:0CF9A182-3DAD-4518-A6A4-66A3F409CE8E@microsoft.com...
> I'm looking for a reference function to pull the result from an out of 
> sort
> table (see table example below). The result should always come from the
> reference value LT A14.  The table is always a whole number 
> 0,1000,2000,3000,
> etc.
>
> I've tried the following functions, but they all require data in ascending
> order and/or exact match of the value in A14.
> =VLOOKUP($A$14,A$2:B$10,TRUE)
> =INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
> =LOOKUP($A$14,A$2:A$10,B$2:B$10)
>
> Table example:
> A B
> 2 1000 0.525
> 3 6000 0.946
> 4 3000 0.675
> 5 0 0.457
> 6 4000 0.746
> 7 2000 0.606
> 8 8000 1.29
> 9 5000 0.835
> 10 7000 1.09
>
> If A14 = 3125, then result in B14 is 0.675.
>
> I don't want to have to use a complex IF/AND, like:
> =IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue
> IF/AND")
>
> Thanks, Kevin 


0
T
3/1/2010 6:17:44 PM
Lars-Åke,

Your suggestion for using the Floor function works perfectly.  The following 
formulas work great for what I needed.

=INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0))
or =VLOOKUP(FLOOR($A$14,1000),$A$2:$B$10,2,FALSE)

-- 
Thanks, Kevin


"Lars-Åke Aspelin" wrote:

> On Mon, 1 Mar 2010 10:01:01 -0800, AFSSkier
> <AFSSkier@discussions.microsoft.com> wrote:
> 
> >I'm looking for a reference function to pull the result from an out of sort 
> >table (see table example below). The result should always come from the 
> >reference value LT A14.  The table is always a whole number 0,1000,2000,3000, 
> >etc.
> >
> >I've tried the following functions, but they all require data in ascending 
> >order and/or exact match of the value in A14.
> >=VLOOKUP($A$14,A$2:B$10,TRUE)
> >=INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
> >=LOOKUP($A$14,A$2:A$10,B$2:B$10)
> >
> >Table example:
> >	A	B
> >2	1000	0.525
> >3	6000	0.946
> >4	3000	0.675
> >5	0	0.457
> >6	4000	0.746
> >7	2000	0.606
> >8	8000	1.29
> >9	5000	0.835
> >10	7000	1.09
> >
> >If A14 = 3125, then result in B14 is 0.675.
> >
> >I don't want to have to use a complex IF/AND, like:
> >=IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue 
> >IF/AND")
> > 
> >Thanks, Kevin
> 
> 
> Try this modifying your second formula like this
> 
> >=INDEX(A$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,1),2)
> 
> or, a little simpler, just
> 
> =INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0))
> 
> Hope this helps / Lars-Åke
> .
> 
0
Utf
3/1/2010 8:15:02 PM
Although this can be done (of course), you (or your principal) are making it 
difficult. Why not sort the table (ascending) so you can use standard 
VLOOKUP functionality?

If that is not possible, please give some more information about the (type 
of) problem you are trying to solve; we are just not prepared to believe 
things should be that difficult.:-)

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel


"AFSSkier" <AFSSkier@discussions.microsoft.com> wrote in message 
news:0CF9A182-3DAD-4518-A6A4-66A3F409CE8E@microsoft.com...
> I'm looking for a reference function to pull the result from an out of 
> sort
> table (see table example below). The result should always come from the
> reference value LT A14.  The table is always a whole number 
> 0,1000,2000,3000,
> etc.
>
> I've tried the following functions, but they all require data in ascending
> order and/or exact match of the value in A14.
> =VLOOKUP($A$14,A$2:B$10,TRUE)
> =INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
> =LOOKUP($A$14,A$2:A$10,B$2:B$10)
>
> Table example:
> A B
> 2 1000 0.525
> 3 6000 0.946
> 4 3000 0.675
> 5 0 0.457
> 6 4000 0.746
> 7 2000 0.606
> 8 8000 1.29
> 9 5000 0.835
> 10 7000 1.09
>
> If A14 = 3125, then result in B14 is 0.675.
>
> I don't want to have to use a complex IF/AND, like:
> =IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue
> IF/AND")
>
> Thanks, Kevin 

0
Niek
3/1/2010 8:18:11 PM
I would be a lot easier if the table was not shared or if it was a perfect 
world.  But as you know, we're all in end-user hell & it's not a perfect 
world.

The table is imported in ascending order.  But the end-users are able to 
sort as they need it printed.

I know my posted example doesn't reflect this, I simplified it for 
illustration.

-- 
Thanks, Kevin


"Niek Otten" wrote:

> Although this can be done (of course), you (or your principal) are making it 
> difficult. Why not sort the table (ascending) so you can use standard 
> VLOOKUP functionality?
> 
> If that is not possible, please give some more information about the (type 
> of) problem you are trying to solve; we are just not prepared to believe 
> things should be that difficult.:-)
> 
> -- 
> Kind regards,
> 
> Niek Otten
> Microsoft MVP - Excel
> 
> 
> "AFSSkier" <AFSSkier@discussions.microsoft.com> wrote in message 
> news:0CF9A182-3DAD-4518-A6A4-66A3F409CE8E@microsoft.com...
> > I'm looking for a reference function to pull the result from an out of 
> > sort
> > table (see table example below). The result should always come from the
> > reference value LT A14.  The table is always a whole number 
> > 0,1000,2000,3000,
> > etc.
> >
> > I've tried the following functions, but they all require data in ascending
> > order and/or exact match of the value in A14.
> > =VLOOKUP($A$14,A$2:B$10,TRUE)
> > =INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
> > =LOOKUP($A$14,A$2:A$10,B$2:B$10)
> >
> > Table example:
> > A B
> > 2 1000 0.525
> > 3 6000 0.946
> > 4 3000 0.675
> > 5 0 0.457
> > 6 4000 0.746
> > 7 2000 0.606
> > 8 8000 1.29
> > 9 5000 0.835
> > 10 7000 1.09
> >
> > If A14 = 3125, then result in B14 is 0.675.
> >
> > I don't want to have to use a complex IF/AND, like:
> > =IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue
> > IF/AND")
> >
> > Thanks, Kevin 
> 
0
Utf
3/1/2010 8:57:03 PM
Reply:

Similar Artilces:

How to get the answer to reference a worksheet
For example - if the answer to a calculation is 18, then look in sheet named "18". If 19, then look in sheet "19". Thanks. Marcus Use the INDIRECT function. E.g., =INDIRECT("'"&A1&"'"&"!A10") This will return the value in cell A10 from the sheet named in cell A1. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Marcus Fox" <please-reply-via-newsgroup-th@-i-posted-to.com> wrote in message news:m4cjg.33798$uP.14385@newsfe2-gui.ntli.net... > F...

Table with FIFO Balances
Can anyone tell me which table holds FIFO balances in it? Thanks, Jocelyn Jocelyn, IV00102 has the quantities but I suspect your looking for IV10200 which has the history and the current FIFO layers. More info here: http://msdynamicsgp.blogspot.com/2007/09/weekly-dynamic-inventory-value-via-sql.html Mark (DynamicAccounting.net) http://www.dynamicaccounting.net On Oct 1, 3:44 pm, Jocelyn <Joce...@discussions.microsoft.com> wrote: > Can anyone tell me which table holds FIFO balances in it? > > Thanks, > Jocelyn ...

Sort by date in Excel 2002?
How to sort by year with this format Sept 9/03 Is the data formated to look like that or is it a text entry? if it is a format. It will sort just using any sorting procedure. if it is text use a helper column with =right(A1,2) "Betty" wrote: > How to sort by year with this format Sept 9/03 > ...

Query for existence of table
Hi all,What sort of SQL query could be used in an Access database to determine the existence of a table? I can do this in SQL server by looking at sysobjects - but how does one do it in Access? I have an application that needs to check to see if a table exists and, if not, create it on-the-fly.Thanks,-bruce create procedure sphappyasif exists (select name from sysobjects where id =object_id('mytable'))begindrop table mytableendOn Mar 20, 4:51 pm, "Microsoft Newsgroups" <broe...@cfl.rr.com> wrote:> Hi all,>> What sort of SQL query could be used in an Access da...

Merge 2 tables with conditions
Hi! I have 2 tables: Table Users: ID Name Password Birth Table Status: ID Status Both tables are related by "ID" field, but both tables have not the same number of elements I want to obtain a new table with this structure: Table Result: ID Name Password Birth Status As I said before both tables have not the same number of elements (rows) so in my result table, "Status" field should be empty for those "ID"'s not found in "Status" table. Example: Users: 1 2 3 ...

Transpose & offset data from a table
I have the following table and I would like to have each item on one row # Acc# Dr Cr # Count Info 1271 1030 4.67 1 3 Cr 2200 0.27 1 3 Dr 6050 4.41 1 3 Dr 1288 1030 7.87 2 2 Cr 6090 7.87 2 2 Dr 1617 1030 61.9 3 4 Cr 2200 2.59 3 4 Dr 6050 4.11 3 4 Dr 6550 55.2 3 4 Dr 1958 1000 45.9 4 5 Cr 1000 39.2 4 5 Cr 2200 4.01 4 5 Dr 6310 37.4 4 5 Dr 6630 43.7 4 5 Dr There is info that can help with determining items Count - checks how many items should be transposed Info - provides the information whea...

sum tables cells using row and column conditions
Hi, i need to sum the values in a table based on a name match in COLUMN B and a text match in ROW 4 - say for each occurrence of "Jim Smith" in range B7:B505,when "text" appears in range E4:GC4, SUM all cells which will contain number values- so if:- text1 text2 text3 text1 Jim Smith 3 6 2 4 Sue Brown 1 5 1 7 Mark Bosman 2 9 3 6 Jim Smith 5 4 2 3 the result would have ...

Problem with TOC using various TOC styles in the same table (Wrd20
Hello, I'm having a weird problem with the table of contents. I have been editing a long document and am regularly updating the TOC. I have just noticed that the spaces between the lines were various in the TOC and thus used the style inspector to find out why. It appeared that TOC 1, TOC 2, TOC 3, TOC 4...styles were all there in the TOC. I have no idea why, I'm creating the TOC repeatedly, removing or replacing the previous one and when creating, I'm choosing TOC 1 as the style but when the TOC is inserted, the problem is the same. Single space here, 1.5 space th...

Is there something like Word's "CREATEDATE" function in Excel?
I've created a lovely invoice in Excel for my company. When I generate a new invoice from the template it puts in the date using the TODAY() function, but unfortunately this updates if I ever open the invoice again to look at it. In Word there is a "CREATEDATE" function which inserts the date that the file was created first, and never after does it change. Is there any way to accomplish this same task in Excel? Thanks, Michael mleenheer @ yahoo.com ML, Keyboard shortcut to date stamp is put your cursor in the cell you want and push -- ctrl ; --(thats ctrl and semicolon...

Duplicate reference to .pst file Outlook 2003
I have Office 2003 including Outlook 2003 installed on a Win XP Pro machine. When Outlook opens two Personal Folders appear in the Mail window, one has the default icon, the second has the archive icon. I cannot delete either of these. In Properties|Advanced they both have the same exact path name to Outlook.pst. In the Calendar window under My Calendars appears Calendar and Calendar in Personal folders. In the Contacts window two sets of contact folders appear As Contacts and Contacts in Personal folders, etc. Likewise for the Tasks and Notes windows - duplicate icons appear. If I dele...

User-defined Functions #2
How can I access the user-defined functions?? I want to modify one of the functions and don't see how to get into it. Thanks in advance... -Andrea Report Writer functions.... shown as user defined functions in calculated fields are created by a Dexterity developer and cannot be created or altered by a user. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessaril...

How to extend functionality
Hi, this is my need: I should add an entry to the context menu in the list of email, a heading such as "Send to" to which I could associate my code (such as VB 6.0 COM component) to perform some "custom" operation on email selected. Recognizing that this question is my very first of its kind, ie although I'm developer for many years I never had to address the need to "expand" the functionality of a package of office. I would be grateful to anyone who was in to give me a hand, at least to address me on how/where retrieve documention for this need...

Data from Excel to Access Table
Hello, I have this code setup in Excel but I want to be able to upload multiple rows at one time. Is there a way to incorporate this into my code? Also, is there a way to upload cells that are blank as well? Right now it won't allow me to upload cells that are blank. Can this be done? Sub UploadData() Dim MyCn As ADODB.Connection Dim SQLStr As String Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\BC Quality Action Database.mdb;SystemDB=\...

New functionalities in version 3.0
Hi, I'm looking for any specyfication of new functionalities in version 3.0. We are writing new functionalites in base of 1.2 version - maybe it will not needed and we spend time. search google for microsoft crm and you will see lots of news items related to version 3.0 -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Rafal" <Rafal@discussions.microsoft.com> wrote in message news:9C72D354-3ACC-479D-9B17-BC65036F978A@microsoft.com... > Hi, I'm looking for any specyfication of new functionalities in version 3.0. > We are writing new functionali...

AutoFill with Reference incrementing BUT able to delete!
Good morning folks! I hope I have a simple one for you but I am stumped! On sheet #1 (called: Kub Kar List) I have my sponsors listed down Col "F" and their slogan in Col "H". On another sheet (SLOGAN) I would like to be able to list the sponsor slogan (='Kub Kar List'!$H3) also incrementing downwards. My problem is if I delete a sponsor from sheet#1 (highlight row and delete) I get a #REF error because that line was deleted and my calc on sheet#2 changes! I would like my reference NOT to change no matter whats done on the first sheet! What ...

Function VBA
Dear expert, I am not good at function VBA, but I know that can help me to save a lot of memory. Say a cell in K10, L10 and M10 Contain figures 7,8,10 How can I write a function say in J10 type =cross(K10,L10,M10) Outcome can be 4. If And(M10>L10, L10>K10) = Show "All DOWN" If And(K10>L10, L10>M10) = Show "All UP" If And(K10>L10, L10<M10) = Show "K cross L UP" If And(K10<L10, L10>M10) = Show "K cross L down" Now I have a lengthy function in J10 which is going to copy from J10 to J32000 =IF(AND(K10>L10,L10&g...

Missing possibility to sort responses in meeting
Hi When opening a meeting in Outlook 2010, it's possible to go to the tracking pane and see who accepted, declined or are tentative. For severel years (since Outlook 2003), I've missed the possiblity to click the 'Response' column, to sort the responses! This must be an easy thing to implement! In addition, I'm missing three reply buttons in the open meeting window; - Reply accepted - Reply declined - Reply tentative Please concider implementing these suggestions! Cheers, Martin ---------------- This post is a suggestion for Microsoft, and Mi...

Replace Function #2
I've used this feature a lot, but can't seem to get this task accomplished... I have a column with numbers, but the first character is an apostrophe ('). I want to removed this character from this column so I can format the column successfully. Thoughts?? Thanks for your help! In a helper column, try: =SUBSTITUTE(A1,"'","") Copy down as needed. Then copy the helper column and using 'Paste Special' ->Values, paste the date back to the original column. Regards, Paul "DNA" <dnoel@fsgbank.com> wrote in message news:c4293322...

Table with 'No Lines'
Using Publisher 2002 .... To prevent tables slipping I've tried Ctrl 'M' and creating a table on this page. I select all and colour the lines in black. (This creates a slipproof template) I then copy using Ctrl 'C' Back to original page, Ctrl 'V', the table I copied is now positioned exactly on top of the under page. I want to change this table into "No Lines" in order that I may type in the data using the 'tab' key to proceed to the next box. *exactly* but I'm unable to carry out this function. I'd appreciate guidance please. John ...

concatenate function limitations
How to format a text in concatenate function. for eg. "mm2" . i want to do in concatenate function , the last no. 2 should be in superscript position. pls suggest answer or alternative -- NITESH G ------------------------------------------------------------------------ NITESH G's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17590 View this thread: http://www.excelforum.com/showthread.php?threadid=482742 Hi One way with your number in A1 this formula in B1 =A1&" mm�" to type the �, hold down the Alt key and type 0178 from the numer...

invisible graphics and tables
I have upgraded my computer to a P4 3Ghz and FX5600 256Mb Graphics Card and New Matrox Hard Drive.. Since formatting and re-installation of PUBLISHER i can not see my Tables or Word art and a few graphics only the Fonts are Visible. The documents print fine everything is there but i can not see them on the monitor. I made up a new File saved it then re executed it and the same thing happens. Have all updates in, changed refresh rates, latest Nvidia driver, Reinstalled Publisher and updates. PLEASE HELP ME BEFORE IT GOES OUT THE WINDOW!!!!!! Why is it that the first thing people wan...

Sort order when using Contacts as Address Book?
When using Contacts as an Address Book, is there a way to change the sort order without changing the Default Full Name order? Currently when I select "To..." while composing a new message, Contacts are sorted by first name. I'd like to sort them by last name. Thanks. -- Lew One of the most frequently asked questions (probably becuase it's one of the most carefully hidden options): Go to Tools > E-mail accounts > View or change existing directories or address books > Outlook Address Book > Change. > Set your sort order there -- Russ Valentine [MVP-Out...

Pivot tables lost in Excel 2003
Hi, We recently upgraded some of our machines to Office 2003, from Office 2000, however are now experiencing serious problems with Excel. It appears that when a file containing pivot tables (all files were created in Excel 2000) is modified with Excel 2003, the pivot tables somehow become corrupt. The next time the file is opened, Excel reports an error and tries to repair the file, dumping the Pivot tables due to a problem with their integrity. The exact message is as below; PivotTable report 'PivotTable1' on '[Book1.xls]Sheet1' was discarded due to integrity problems. Pivo...

How to reduce excel file size having pivot tables
Hi, I am developing a C# application to create Excel Pivot tables. I have developed the same by using methods in the Microsoft.Office.Interop.Excel. But the size of the output excel files is too large. Is there any option to reduce the file size. Given below the code used to generate Excel File . private string CreateReport(Excel.Application xlApp, string queryReports, string reportPath) { try { bool IsErrorInWorkBook = false; string strGrandTotal = null; FileInfo fpPubReports = new FileInfo(...

Pivot Table Processing
I am using a pivot table to show the amount of product that is being used in a month. What I would like to do is color code any month that has less than 20 days usage in any month. This is part of a table that I am using: Product Days Qty Orderld Month 1234 23 12 60 1 2345 26 90 60 1 9586 18 85 60 2 4759 21 65 60 3 2589 12 34 60 4 2589 23 56 60 5 1234 10 89 60 6 1234 23 12 60 ...