Lookup vertically

In cell C25 I would like to place a formula that finds the closest cell
UP in Column A that contains the word "Arizona", and then returns the
value in column B for that row. For example: if A19 contains "Arizona"
and B19 contains 34.... and A14 contains "Arizona" and B14 contains 38,
I would like the formula in cell C25 to return 34--because row 19 is
closer to row 25 than row 14 is to row 25. (assume the other rows in
column A contain names of different states). Thanks very much to whoever
can help!!!

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **
0
hugh
7/23/2004 8:23:06 AM
excel 39879 articles. 2 followers. Follow

1 Replies
303 Views

Similar Articles

[PageSpeed] 53

=INDIRECT("B"&MAX(IF(A$1:A24="Arizona",ROW(A$1:A24))))

array entered (Ctrl-Shift-Enter)

Jerry

hugh wrote:

> In cell C25 I would like to place a formula that finds the closest cell
> UP in Column A that contains the word "Arizona", and then returns the
> value in column B for that row. For example: if A19 contains "Arizona"
> and B19 contains 34.... and A14 contains "Arizona" and B14 contains 38,
> I would like the formula in cell C25 to return 34--because row 19 is
> closer to row 25 than row 14 is to row 25. (assume the other rows in
> column A contain names of different states). Thanks very much to whoever
> can help!!!
> 
> ** Posted via: http://www.ozgrid.com
> Excel Templates, Training, Add-ins & Software!
> http://www.ozgrid.com/Services/excel-software-categories.htm **
> 

0
post_a_reply (1395)
7/23/2004 11:19:16 AM
Reply:

Similar Artilces:

Drop Lookup from Jet table field
My table was created using design mode and has a lookup table. I now need to remove the lookup table using VBA ie docmd.runsql "ALTER TABLE mytable COLUMN myfield DROP LOOKUP" I have been unable to find any documentation on how to do this but any suggestions would be appreciated. ras wrote: >My table was created using design mode and has a lookup table. >I now need to remove the lookup table using VBA > >ie >docmd.runsql "ALTER TABLE mytable COLUMN myfield DROP LOOKUP" > >I have been unable to find any documentation on how to do this but any >s...

Vlookup formula where lookup value is a result a formula (receive.
Vlookup formula where lookup value is a result a formula (receive #N/A error) How fix? Without seeing what your formula is, it's impossible to do more than guess... In article <1B061D63-FA8B-4C96-BF0B-C3B706433CCF@microsoft.com>, cpitta <cpitta@discussions.microsoft.com> wrote: > Vlookup formula where lookup value is a result a formula (receive #N/A error) > How fix? Maybe: =if(iserror(vlookup(...)),"some error message",vlookup(...)) or maybe... =if(isna(a1),"",vlookup(a1,sheet2!a:e,3,false)) cpitta wrote: > > Vlookup formula wher...

TEsting Reverse DNS lookup
Hi, I have enabled reverse dns lookup in my smtp virtual server. How do i test this feature? In news:E88D7CC9-D9E9-4412-B884-61CC0DA8E62C@microsoft.com, Jack Dorson <JackDorson@discussions.microsoft.com> typed: > Hi, > > I have enabled reverse dns lookup in my smtp virtual server. Why? > How do i test this feature? I guess I'd first have to ask, what is it you want to accomplish with this? On Thu, 8 Jun 2006 08:40:02 -0700, Jack Dorson <JackDorson@discussions.microsoft.com> wrote: >Hi, > >I have enabled reverse dns lookup in my smtp virtual s...

Sumif/SumProduct/lookup question
I want to insert subtotals only for items containing at least two specified costs. A B C 1 Item Name Cost Title Cost Value 2 Item 1 Cost 1 5 3 Item 1 Cost 2 3 4 Item 1 Cost 3 4 5 Item 1 Cost 4 1 6 Item 2 Cost 1 9 7 Item 3 Cost 3 6 This setup continues for 28,000 rows. I want to separately sum all the cost values for each item number which contains both a cost 1 and cost 2. Are conditional subtotals possible, so that onl...

Difficult problem (for me !) Lookup or something that i need to use ???
This is a multi-part message in MIME format. ------=_NextPart_000_00D7_01CA7B43.621ACFB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi there, - The range (see near the end of this mail) where all the "X" are in is = called "Risks" (a,b,c,... & Step 1,... Step X are not included in ths = range). - The line with the text a,b,c,d,e,f,...above the range "Risks" have all = text in them (example d=3D "Electricity") - The cells in the left "Step 1",... refer to a sheet ...

Need Help with lookup values in Table
I have a table Like This: Tube Diameter 15 14 13 12 11 10 9 15,000 60 54 50 45 40 36 32 12,000 45 42 39 35 32 29 26 9,000 33 30 29 26 24 21 18 7,500 26 24 22 21 19 17 15 6,000 20 19 18 16 15 13 12 5,000 17 16 15 12 11 10 8 4,000 13 12 11 10 9 8 7 3,000 10 9 9 8 7 6 5 2,000 7 6 6 5 5 4 4 Transfomer Size The table data represents the Maximum footage (of Neon Tubing) allowed for a particular transformer size based on the Tube Diameter. The information that I want to input is: 1. Tube Diameter 2. Actual Footage I need to write a Lookup f...

lookup question
I am having trouble figuring out how to look something up in a list based on conditions. For example I have: Store # State Open Date 1 ND 1/2/03 2 SD 2/4/03 in a list with approx 350 records which all have this same info. I need to create another table that shows which stores opened in which state in each month. So, I need to say if the open date is in January (or between Jan 1 & Jan 31) & the state is ND then I need the cell the formula is in to read 1. Basically if the op...

Item Lookup Code #2
Does the item lookup code have a maximum number of characters it should be? If it is too long, does the bar code become unreadable? What would be max number of characters to have for the item lookup code? Thanks 25 Maybe, see next Depends on your barcode scanner's readable field width. Most lasers read longer/wider barcodes than CCDs. If you will ever need to remember the ILC, then like the telephone company used to say, 7 digits max. -- * "Nick @ REAL" <NickREAL@discussions.microsoft.com> wrote in message news:EF8D06C1-6DE5-47BB-B3E8-8ABEC1C79148@microsoft.com...

lookup view (find columns) doesn't work for "send email" functionality
When I add new find columns (p.e. parent customer) to my lookup view of my contacts, everything works just fine for lookups... But i recently discovered that this doesn't work if you p.e. open up an account record; you click the button "send email" on the toolbar (or just when you create a new email activity) , which will open a new email screen in MS CRM; and if you then click on the magnifying glass for the to;cc and/or bcc fields... If I select to address a contact and try to search on the parent customer (newly added find column for lookup view) I get no results! (only if i...

3.0 Lookup Records
Is there a way to make the defualt lookup Contact instead of Account? For example, if your creating a case and click the lookup for Customer, we generally need to find the contact vs. the account. ...

Suggestion
A suggestion which a number of customers always ask for (and from another development background I can clearly understand why.) Scenario; you have an Account field on the form along with a Contact field (with whatever field labels are appropriate.) The user selects an Account and (due to the nature of the data) want to select a Contact, but a contact that is related to the Account (as it’s parent.) i.e. they only want to be able to choose contacts for that Account and not any of the others. This currently isn’t possible – you are able to pick any contact (which I can understand why, bu...

Overlap Cells Vertically without Merging
WITHOUT MERGING THE CELLS:- I want to : VERTICALLY, over the space of lets say about 20 cells, (I will Color that Area of 20 Cellsin one color) I want to write a long sentence (in One Cell) and have THAT text over lap all the other 19 cells. NO TEXT will be written in ANY of those other 19 Cells. Is that Possible? Please help! No. Although text can overlap other empty cells on the same row (i.e. horizontally), you can not do this vertically. Hope this helps. Pete On Dec 8, 8:59=A0am, Muffi Zainu <MuffiZa...@discussions.microsoft.com> wrote: > WITHOUT MERGING THE...

Tab Order for Multiple Entry Form. Vertical not horizontal tabbin
Hi there, Does anyone know how to make the tab order go vertically along columns in a multiple entry form? It seems like that tabs will always goes along a row, so you have to tab through every field in a row before it will tab to the next row. I have already tried to do this through the tab order button in the arrange tab, but it doesn't help. If you don't need to tab into certain fields, you can open the form in design mode. Open up Properties and go to the Other tab. Next click on the fields that you don't want to tab into while holding the Shift key. Th...

Add vertical line at intersection of 2 curves
Hi folks, Picture, if you will, an Excel graph with 2 bell-curves plotted on it...one curve shows the freqency distribution of measurement values at "good," or reference, sites, and the other shows the distribution of values at "bad," or impaired, sites. At some point on the graph the curves intersect (hopefully at the inflection points, assuming a more-or-less normal distribution for both populations of sites). The "curves" are actually XY scatterplots with smoothed lines and no markers, using data sorted into uniform "bins" generated by Tools...

auto fill data into a cell from a lookup table
Does anyone know how to set up a cell formula so that if the data in a cell (such as a county name) is entered, an amount is placed in the cell in the next column? Hi use VLOOKUP to do this say you have your list of countries and amounts on sheet 2 in the range A1:B100 and then on sheet 1 you want to type a country name in cell A1 and have the amount appear in B1 the formula for B1 would be =VLOOKUP(A1,Sheet2!$A$2:$B$100,2,0) which says, look at the value in A1, find it in the first column of the table in Sheet2 and return the associated value from the second column of this table whe...

How to add a vertical line to a Line Chart that aleady has 2 Y axi
I want to add a vertical line to a chart to call out a specific date. I have two lines and am using 2 Y axis due to the difference in values of the two data series. For a given X value I want a vertical line to appear on the chart to make it clear that data to the right of the line is AFTER a certain thing was done. How do I do this? http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ ISUTUBBS wrote: > I want to add a vertical line to a chart to ca...

Lookup value with multiple results
I have a 3 column list and need to pull the value from column 2 whenever there's a value in column 1 for that row. I've been using the following formula to accomplish this: {=IF(ISERROR(INDEX($A$1:$C$99,SMALL(IF($A$1:$B$99=ControlSheet!$H$6,ROW ($A$1:$B$99)),ROW(1:1)),2)),"",INDEX($A$1:$B$99,SMALL(IF($A$1:$B$99=ControlSheet!$H$6,ROW($A$1:$B$99)),ROW(1:1)),2))} This works just fine, but my list is actually 9999 rows and not 99. When I change the array from just 99 rows up to 9999 rows, I get no values returned. I think this is because it's too big to calculate. Can ...

LOOKUP function #7
OS =XP Pro Office 3 LOOKUP function, VERY simple formula, just will not work. Is there something special, an unobvious characteristic about this one? Playing back how I am using it : =LOOKUP(x1,x3:x20,y3:y20) where search in column x3 through x20, for value in x1, returns corresponding contents in adjacent column y3 through y20. Simple. Right? Why will it not work? Appreciative, Wayne On Mar 8, 4:58=A0pm, wgd.roam...@verizon.net wrote: > LOOKUP function, VERY simple formula, just will not work. > Is there something special, an unobvious characteristic > about this one? N...

all int & ext message stuck in message awaiting directory lookup q
last week my exchange 2003 (SBS) was unable to mount the Mail store and public store after a memory dump (restart) incident. But i able to mount the store by using eseutil. after that i notice one of the client account (only for internal mail, no external email account) will receive a copy of all the incoming and outgoing e-mail from the exchange server. Since i deleted and recreate this client account. no mail has been delivered to any mail client. All the int & ext message stuck in "message awaiting directory lookup" queue. All service has started without any error. I al...

Vertical tab control with owner draw enabled
Hi I have a tab control with owner draw enabled in which I am drawing a icon and a text. It works perfectly fine when the TCS_VERTICAL option is not selected. But when I select TCS_VERTICAL option I am not able to see the text. Could anyone knows a solution for this? Regards ...

Lookup Problem Matching on Two Criteria
I have a lookup/match problem that I cannot seem to get a solution to. I am assuming I can use a combination IF statement with a nested VLOOKUP but it's not coming to me. I have a table with three columns. Column A has values S01 to S08. Column B has unique values but are associated with the values in column A. Column C has the target values I am trying to populate in a cell. So the problem is that I need to match a Column A value with a column B value to find the answer in column C. Please note that the values in Column A are duplicated multiple times depending on their relationship to ...

Adding a lookup field to the incident form
Hi, When selecting for an account in the incident form - I would like to have the option to search the accounts by the account name (existing) and also by the account number. 1) How can I add this option to the account's lookup form (that is opened from the incident form) ? 2) As an alternative - how can I add the account number to the incident form (as a new foreign field from the account in the incident's form) ? many thanks Ofer "Ofer" <Ofer@discussions.microsoft.com> wrote in message news:EF4A0CAE-26F2-4373-840F-90B67C019F5A@microsoft.com... > Hi, > ...

Broke mail
Whats causing this ? only thing in the event viewer is Event Type: Warning Event Source: Marc3Host Event Category: None Event ID: 6204 Date: 21/02/2007 Time: 17:54:30 User: N/A Computer: XXXXXX Description: Plugin DSScheduler reports exception. Data Sources: Builder failed. Additional info: Could not resolve url For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. been trying to remove a 2007 server but its infected everything it seems Marc3Host? What's that? You've probably got something else installed on the Exchange server....

Lookup for once #2
Thanks Dave for your help. But is there a way that you could input a formula instead of having the advanced filter? Thank you, Kevin ------------------------------------------------------ Maybe Data|Filter|Advanced filter|Unique Records only. See Debra Dalgleish's site for nice instructions. http://contextures.com/xladvfilter01.html#FilterUR Kevin Lin wrote: > > Hi All, > > Need help on the following. > > A V > 1 AAA001 AAA001 > 2 AAA001 BBB001 > 3 AAA001 BBB002 > 4 BBB001 > 5 BBB001 > 6 BBB002 &...

Count condition true for 3 lookup columns
Hi All, I have a worksheet that contains 3 seperate lookup columns. My challenge is that I need a low overhead way to find and count the combination of true lookups. So if one of the 3 looksup is found I would get a return of '1', if 2 of the 3 are found then '2'. I know I can do this with an array formula, but am worried about the calculation hit as this is a very complex worksheet and already has a long intitial calc time. I suppose I could do the inverse and could an 'iserror' condition and then subract that answere from 3. I thought of using a complex 'if&...