Lookup based on reference

The other day, I used this formula to lookup table data based on the row and 
column such as:
=INDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1-FLOOR($S$1,0.1),0.01),$R$2:$AA$2,0))

So that If I had the number 2.22, it will return for me .15 from the table
lookup table (made up values)
       .00  .01  .02  .03  .04  ... .09
2.0  .31  .32  .33  .34  .35  ...  .50
2.1  .21  .35  .51  .51  .25  ...  .85
2.2  .53  .52  .15  .52  .51  ...  .81
2.3  .89  .58  .23  .45  .15  ...  .15

My question now is, based on the above formula, how would I be able to do 
the opposite. So if I have the indexed value; say .15, and I want it to 
return 2.22?

-- 
Thank you!
0
Utf
4/30/2010 1:17:01 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
658 Views

Similar Articles

[PageSpeed] 24

How the formula is supposed to know whether the .15 'means' 2.22 or
2.39 (as they both would return .15)?

On Apr 30, 2:17=A0pm, Doug <D...@discussions.microsoft.com> wrote:
> The other day, I used this formula to lookup table data based on the row =
and
> column such as:
> =3DINDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1=
-FLO=ADOR($S$1,0.1),0.01),$R$2:$AA$2,0))
>
> So that If I had the number 2.22, it will return for me .15 from the tabl=
e
> lookup table (made up values)
> =A0 =A0 =A0 =A0.00 =A0.01 =A0.02 =A0.03 =A0.04 =A0... .09
> 2.0 =A0.31 =A0.32 =A0.33 =A0.34 =A0.35 =A0... =A0.50
> 2.1 =A0.21 =A0.35 =A0.51 =A0.51 =A0.25 =A0... =A0.85
> 2.2 =A0.53 =A0.52 =A0.15 =A0.52 =A0.51 =A0... =A0.81
> 2.3 =A0.89 =A0.58 =A0.23 =A0.45 =A0.15 =A0... =A0.15
>
> My question now is, based on the above formula, how would I be able to do
> the opposite. So if I have the indexed value; say .15, and I want it to
> return 2.22?
>
> --
> Thank you!

0
AB
4/30/2010 1:42:59 PM
I think we need more. Suppose the know value was 0.51 (which occurs more 
than once), would you want 2.1 or 2.2 returned?
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Doug" <Doug@discussions.microsoft.com> wrote in message 
news:F4EDE8F6-29F5-4799-A833-4CC84A3BD87A@microsoft.com...
> The other day, I used this formula to lookup table data based on the row 
> and
> column such as:
> =INDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1-FLOOR($S$1,0.1),0.01),$R$2:$AA$2,0))
>
> So that If I had the number 2.22, it will return for me .15 from the table
> lookup table (made up values)
>       .00  .01  .02  .03  .04  ... .09
> 2.0  .31  .32  .33  .34  .35  ...  .50
> 2.1  .21  .35  .51  .51  .25  ...  .85
> 2.2  .53  .52  .15  .52  .51  ...  .81
> 2.3  .89  .58  .23  .45  .15  ...  .15
>
> My question now is, based on the above formula, how would I be able to do
> the opposite. So if I have the indexed value; say .15, and I want it to
> return 2.22?
>
> -- 
> Thank you! 

0
Bernard
4/30/2010 1:48:18 PM
Array entered with query value in cell T1

=INDEX(Q:Q,MIN(IF($R$3:$AA$41=T1,ROW($R$3:$AA$41))))+
INDEX(2:2,MIN(IF($R$3:$AA$41=T1,COLUMN($R$3:$AA$41))))

-- 
Jacob (MVP - Excel)


"Doug" wrote:

> The other day, I used this formula to lookup table data based on the row and 
> column such as:
> =INDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1-FLOOR($S$1,0.1),0.01),$R$2:$AA$2,0))
> 
> So that If I had the number 2.22, it will return for me .15 from the table
> lookup table (made up values)
>        .00  .01  .02  .03  .04  ... .09
> 2.0  .31  .32  .33  .34  .35  ...  .50
> 2.1  .21  .35  .51  .51  .25  ...  .85
> 2.2  .53  .52  .15  .52  .51  ...  .81
> 2.3  .89  .58  .23  .45  .15  ...  .15
> 
> My question now is, based on the above formula, how would I be able to do 
> the opposite. So if I have the indexed value; say .15, and I want it to 
> return 2.22?
> 
> -- 
> Thank you!
0
Utf
4/30/2010 2:35:01 PM
I am sorry, let me give more detail.
These are the actual values in a portion of the table; If cell "T1" says 
".12" I need it to first recognize the closest value, being between 0.1179 & 
0.1217. Second since it is closer to 0.1217 it would return the value ".31". 
How can this be accomplished please?

	0.00	(0.01)	0.02	0.03	
0.0	0.0000	0.0040	0.0080	0.0120	
0.1	0.0398	0.0438	0.0478	0.0517	
0.2	0.0793	0.0832	0.0871	0.0910	
(0.3)	0.1179     (0.1217)	0.1255	0.1293	
0.4	0.1554	0.1591	0.1628	0.1664
0.5	0.1915	0.1950	0.1985	0.2019	
0.6	0.2257	0.2291	0.2324	0.2357	
0.7	0.2580	0.2611	0.2642	0.2673	
0.8	0.2881	0.2910	0.2939	0.2967	
0.9	0.3159	0.3186	0.3212	0.3238	
1.0	0.3413	0.3438	0.3461	0.3485	
1.1	0.3643	0.3665	0.3686	0.3708	

-- 
Thank you!


"Jacob Skaria" wrote:

> Array entered with query value in cell T1
> 
> =INDEX(Q:Q,MIN(IF($R$3:$AA$41=T1,ROW($R$3:$AA$41))))+
> INDEX(2:2,MIN(IF($R$3:$AA$41=T1,COLUMN($R$3:$AA$41))))
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Doug" wrote:
> 
> > The other day, I used this formula to lookup table data based on the row and 
> > column such as:
> > =INDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1-FLOOR($S$1,0.1),0.01),$R$2:$AA$2,0))
> > 
> > So that If I had the number 2.22, it will return for me .15 from the table
> > lookup table (made up values)
> >        .00  .01  .02  .03  .04  ... .09
> > 2.0  .31  .32  .33  .34  .35  ...  .50
> > 2.1  .21  .35  .51  .51  .25  ...  .85
> > 2.2  .53  .52  .15  .52  .51  ...  .81
> > 2.3  .89  .58  .23  .45  .15  ...  .15
> > 
> > My question now is, based on the above formula, how would I be able to do 
> > the opposite. So if I have the indexed value; say .15, and I want it to 
> > return 2.22?
> > 
> > -- 
> > Thank you!
0
Utf
4/30/2010 3:41:01 PM
Doug,
You still have not given us the complete picture
The number that is closest to 0.21 is 0.2019 in the 0.4 column of the 0.5 
row
How are we to know which row or column to search?
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Doug" <Doug@discussions.microsoft.com> wrote in message 
news:803DAAB6-BA0F-491F-A362-72BD008A1CFA@microsoft.com...
> I am sorry, let me give more detail.
> These are the actual values in a portion of the table; If cell "T1" says
> ".12" I need it to first recognize the closest value, being between 0.1179 
> &
> 0.1217. Second since it is closer to 0.1217 it would return the value 
> ".31".
> How can this be accomplished please?
>
> 0.00 (0.01) 0.02 0.03
> 0.0 0.0000 0.0040 0.0080 0.0120
> 0.1 0.0398 0.0438 0.0478 0.0517
> 0.2 0.0793 0.0832 0.0871 0.0910
> (0.3) 0.1179     (0.1217) 0.1255 0.1293
> 0.4 0.1554 0.1591 0.1628 0.1664
> 0.5 0.1915 0.1950 0.1985 0.2019
> 0.6 0.2257 0.2291 0.2324 0.2357
> 0.7 0.2580 0.2611 0.2642 0.2673
> 0.8 0.2881 0.2910 0.2939 0.2967
> 0.9 0.3159 0.3186 0.3212 0.3238
> 1.0 0.3413 0.3438 0.3461 0.3485
> 1.1 0.3643 0.3665 0.3686 0.3708
>
> -- 
> Thank you!
>
>
> "Jacob Skaria" wrote:
>
>> Array entered with query value in cell T1
>>
>> =INDEX(Q:Q,MIN(IF($R$3:$AA$41=T1,ROW($R$3:$AA$41))))+
>> INDEX(2:2,MIN(IF($R$3:$AA$41=T1,COLUMN($R$3:$AA$41))))
>>
>> -- 
>> Jacob (MVP - Excel)
>>
>>
>> "Doug" wrote:
>>
>> > The other day, I used this formula to lookup table data based on the 
>> > row and
>> > column such as:
>> > =INDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1-FLOOR($S$1,0.1),0.01),$R$2:$AA$2,0))
>> >
>> > So that If I had the number 2.22, it will return for me .15 from the 
>> > table
>> > lookup table (made up values)
>> >        .00  .01  .02  .03  .04  ... .09
>> > 2.0  .31  .32  .33  .34  .35  ...  .50
>> > 2.1  .21  .35  .51  .51  .25  ...  .85
>> > 2.2  .53  .52  .15  .52  .51  ...  .81
>> > 2.3  .89  .58  .23  .45  .15  ...  .15
>> >
>> > My question now is, based on the above formula, how would I be able to 
>> > do
>> > the opposite. So if I have the indexed value; say .15, and I want it to
>> > return 2.22?
>> >
>> > -- 
>> > Thank you! 

0
Bernard
5/1/2010 1:22:14 PM
Reply:

Similar Artilces:

Subject based filtering of DSN messages
I can't make this work for the vast majority of the DSN messages we get (message undeliverable and such). I have setup rules to filter the different bounce messages that I see in subject lines and some of them work fine. Most do not. The messages the come from postfix (for instance) have mime-encoded notification messages, split in several parts. I've tried matching the Subject: line I end up seeing in Outlook and the subject line that is in the internet headers of the message, which is not what outlook shows me. I think exchange 2003 is recognizing these messages and try...

Lookup/Find help
Windows XP Professional Office 2000 Hypothetical, but hopefully you'll get the gist of it: I have two worksheets. On worksheet #1, I have two columns. First column is a list of entire workgroup by name and 2nd column is the hours worked. On the second worksheet I simply have an list of names that is a subgroup of those on the first page. These indicate a target group. EXAMPLE Worksheet #1 Sam 35 Joe 37 Mary 20 Beth 41 Ted 38 Worksheet #2 Joe Beth Now, on the first worksheet, I want to add a third column for summing only the target workgroup. Basically, I need a function...

Can I set up a calendar lookup for date selection
Is it possible to lookup a calendar for date selection on a cell. I cannot find any reference to it in the help. try this url http://www.fontstuff.com/vba/vbatut07.htm ================= "Tangoxray" <Tangoxray@discussions.microsoft.com> wrote in message news:72D4DB63-1D7F-477A-93C7-2F767A207B26@microsoft.com... > Is it possible to lookup a calendar for date selection on a cell. I cannot > find any reference to it in the help. ...

view the reference and not the value
Dear All, I am trying to bring in 5 sheets a value (text) from the first sheet. When i typing the equal (=) in the sheet2 i saw the reference and not the value. Any solution for that ? i am using the XP edition Thanks in advance Manos Hi 1. check that your target cell is not formated as 'Text'. change the format to 'General' and re-enter the formula 2. If this does not help goto 'Tools - Options - View' and uncheck 'Formulas' >-----Original Message----- >Dear All, > > >I am trying to bring in 5 sheets a value (text) from the >firs...

changing lookup columns
Anybody know a way to add a column to the LookUp screen for Accounts? When I am in a contact record and want to choose a parent account, I would like more columns to distinguish between the different accounts that come up. This isn't the Advanced Find or Associated View for Accounts so I can't see where to add the columns. I've even checked the QueryBase table and don't see any one that looks like it applies. Thanks. There is no supported way to do this in v.1x. Check this folder if you feel curious: \\CRMSERVER\c$\Inetpub\wwwroot\_controls\lookup\lookupxml "A...

cell to have comma-delimited values based on text
I have a table called "220_reference" with a column name "Part Number" having a sample value of below: 4047122(All Dash no.), 4057222(All Dash no.), 4058222(All Dash no.), 4060122(All Dash no.) The entire value is in one cell representing the "Part Number" column(defined as general type so text I suppose). Simple enough. But what I need to do is take any number that has "(All Dash no.)" after it and search through a column in another table to retrieve any rows that have that number(text) in it. The other table name is "220" with ...

how do i semi-lock a group of cells to allow lookups formula...
how do i semi-lock a group of cells to allow lookups formulas without allowing user changes Do you mean you want to be able to reference these cells in lookup formulas contained in other cells? If so, leaving these cells locked (Format | Cells | Protection), and then Protecting the Sheet (from teh Tools Menu) should be enough. You may want to make sure you have Select Locked and Select Unlocked Cells Checked, if a list of Protection Options pops up (Excel 2002 and later). tj "quander" wrote: > how do i semi-lock a group of cells to allow lookups formulas without > allo...

Lookup #5
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 ...

sum items in a table based on description
I am interested in summing items is a table based on their description. Is there a formula that will add items together from column based on the contents in another cell reference in the same row? I know a pivot table will do this with some restrictions in the table design. You can use sumif if there is onbly one condition Assume you want to sum values in column C where column B is "x" =SUMIF(B2:B500,"x",C2:C500) Regards, Peo Sjoblom "Hrider" <hrider@yahoo.com> wrote in message news:ejggYE$MFHA.3328@TK2MSFTNGP14.phx.gbl... >I am interested ...

Receive PO based on amount.
Greetings GPLings.. :) Is there any way to receive with invoice , a PO that contain service /non inventoried items based on the amount? Eg: PO amount $100000000 for ONE service item and i would want to receive and invoice only $ 200. The reason i used this huge amount as PO is that even if i used a decimal value to receive the PO as a percentage,it will still not be accurate as GP allows only 5 decimal places. In a simple form, i want to receive based on the amount rather than qty. Cheers.. The only way to do that is to enter the PO for a quantity of 100,000,000 at a unit...

Vendor Lookup Field with Modifier
I would like to modify the ap entry form and create 1 or 2mores field. This additional field will be used to track related party vendor number/name For example, the invoice is being paid to Vendor A. But because our invoice for Vendor A may be related to Vendor B, I would like to enter this info in the system. Currently, we just enter the related vendor ID in the note field. But due to the large number of related party transactions, the users are entering incorrect vendor numbers or they are not properly formatted , in the note field. This makes it difficult to analyze related party...

Join based on next closest value (like Excel VLOOKUP)
Trying to do something similar to a VLOOKUP (Excel) in an Access 2003 query. I have the following tables: JOBS Job,Quantity A,96 B,256 C,300 D,4299 COSTS Quantity,Cost 0, $1000 100, $1200 200, $1500 300, $2000 400, $2500 500, $3000 I need a query that takes JOBS.Quantity, looks it up in COSTS.Quantity and find the cost for the NEXT LOWEST quantity. (Example: Job B has a quantity of 256 and the next LOWEST quantity from COSTS is 200 so Job B costs $1500.) The results should be as follows: JOBS.Job,COSTS.Cost A,$1000 B,$1500 C,$2000 D,$3000 This would be ...

Home based Job For Indian
Want the pleasure of life, don't worry, work 1-2hours/day and earn Rs. 10,000-15,000/month online from your home/cafe/office PC. For details write # ejob1189@rediffmail.com Please write the subject of mail as: "Job Inquiry" ...

Fast alternative to table based state transition matrix
I got this answer from comp.theory. It was completely obvious once it was explained. It is trivially simple to create a DFA based recognizer without a state transition matrix data table. Simply encode case statements corresponding to inputs within the case elements of a case statement corresponding to states. In at least some cases the (case within case) method might be faster depending upon whether or not memory is reduced enough to more than offset the higher case statement overhead to increase cache locality of reference. "Peter Olcott" <NoSpam@OCR4Screen.com...

dependent combo box list, with indirect reference
I would like to create a dependent combo box. I have a cell linked to anothe combo box, say C1. I have one list in D1:D4, another list in E1:E3. In C2, I have value "D1:D4", in C3 I have "E1:E3". For the input range for the combox, I tried "=INDIRECT(IF(C1=1,C2,C3))", but only got an empty list. Is this 'illegal'? Thanks, Iyue Debra Dalgleish shows how to do this with Data|Validation. http://www.contextures.com/xlDataVal02.html Maybe you could use her technique on your comboboxes. If no, then post back with a little more info... Comboboxes from the ...

Need help getting query to reference a field in a subform
I am trying to run a query using a specific field in a subform as the criteria. The query is displaying one combo box from the Form, as well as several fields from its own subform. My Form name is: FormGrantManagement My Subform name is: TableMOA subform My Subform's subform is: TableCostReports The field in the subform I want to reference is: MOAID I have found other help topics that lead me to think that a criteria along the lines of [Forms]![FormGrantManagement]![TableMOA subform]! [Form]![MOAID] is what I need to use, and I have tried multiple variations of this but witho...

Colouring Emails based on Headers
I am trying to use colour to pick out spam identified by a Spam program in an X-Header. I have gone into the advanced settings, but can't seem to find an option to colour based on content of the headers. Can anyone help me out here? SB Take a look at HeaderToCategory for MS Exchange 2000/2003 application: http://www.ivasoft.biz/hc.shtml It could help in that case. -- Rregards, Victor Ivanidze, software developer > I am trying to use colour to pick out spam identified by a Spam program in > an X-Header. > I have gone into the advanced settings, but can't seem to fin...

Payroll deduction based on percentage of another deduction
Is it possible to set up a payroll deduction based on percentage of another deduction? I see we can base a deduction on selected paycodes but not deductions. We can collect a percentage of wage attachments as collection fees but if the wage attachment deduction amount changes each pay, we'd have to manually change to collection fee code each pay. I'm looking for a way to calculate this collection fee automatically. ...

Row highlight based on the 1-31 day of the month
I've got a table with dates displayed in the first column (ascending order). I would like to highlight each row based on the day of the month with one of 5 chosen colours (red, yellow, green, blue and orange). rows with the 1st of the month: red rows with the 2nd of the month: yellow ....... rows with the 6th of the month: red ....... Julian. You would use conditional formatting to display different colors based upon the day value. Your conditional formatting formula would look at the day value of the date. You will have multiple conditions for each row. Condition 1 Formula is: ...

Table Lookup formula where 2 known values are inside array
I am using Excel. Please help, I am stuck on a non-typical table lookup I regularly use =lookup to get values where the relationship is 1:1 e.g. in a cell, I put a value, I use =lookup for that value's meaning I need to do this, but the lookup is a table, not a 1:1 column match Row 1 is a header, one of my search values Column 1 is a value I want to find the table (array) contains the other known value I need do do a enter values in 2 cells, and have a lookup return a value based on the value it looks up. eg: -- | 1 | 2 | 3 | 4 w | a | d | h | i x | b | e | i | ...

Outlook 2003
I have four different accounts setup in Outlook. I know that it is possible to use a default signature for new messages and replies with each account. However, when selecting New to start a new message, naturally, it uses the default mail account and inserts the corresponding signature. However, when changing the sent from account in the Accounts drop down, one would expect the "default" signature to be used; i.e. Outlook would delete the signature that it initially inserted and replace it with the default signature for the selected account. This is not what happens. Seemingly, ...

Chip Pearson's VB Object references
I recently distributed a 2007 workbook that contained pop up calendars courtesy of the Microsoft Office Calendar Control 2007. I foolishly assumed that when a user opened the workbook, Excel would “realise” that there was a Calendar control embedded and automatically enable the VB reference. Optimistic eh? I was looking at ways to automate the enablement of the Calendar reference object – I found this code on Chip Pearson’s site and wondered if a similar thing could be run to automatically select the Calendar control? This code enables the Microsoft Visual Basic Application Ex...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

Conditional Formating (how to use Offset() in cell reference)
Using XL 2003 & 97 How do enter into VBA code; the cell Offset R-12,C into the Contitional Formatting below? (I want the "not equal to" referrence a cell 12 rows above in the same column) Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=$J$6397" TIA Dennis This might help you get to the next step: Option Explicit Sub testme01() With Selection If .Row < 13 Then 'too high in the worksheet Else 'remove any existing format first????? .FormatConditions.Delete .FormatConditions.Add ...

Embedded lookup
Can a lookup be embedded in another lookup? A file has separate sheets for each month with identical formats. A file has 13 sheets, one for each month and a YTD sheet. Each sheet has P&L data for multiple companies and the YTD sheet has a data entry field to identify the current month. Based on the value in that field, the lookup function should, first, go to the correct month's sheet, then lookup the correct value. Ex: YTD Cell A1=4 for April; All sheets Cell A3=Sales; Col B = ABC, Inc. The lookup formula will lookup A1, go to the April sheet, then perform the looku...