How to determine the values? 06-05-10

Does anyone have any suggestions on how to determine the value?
For example,
Under following columns:
[A] [B] [C] [D]
1 174 198 222
7 180 204 228
9 182 206 230

A given number is 204 in cell E1, I would like to determine the value under 
column A, which should return 7, because 204 is under the same row.

A given number is 174 in cell E1, I would like to determine the value under 
column A, which should return 1, because 174 is under the same row.

Does anyone have any suggestions on how to do it in excel?
Thanks in advance for any suggestions
Eric
0
Utf
6/5/2010 2:01:27 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1008 Views

Similar Articles

[PageSpeed] 4

Try this array formula** :

=MAX((B1:D3=E1)*A1:A3)

** 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


"Eric" <Eric@discussions.microsoft.com> wrote in message 
news:171FE363-4CB4-49E9-8931-F813AE142C0E@microsoft.com...
> Does anyone have any suggestions on how to determine the value?
> For example,
> Under following columns:
> [A] [B] [C] [D]
> 1 174 198 222
> 7 180 204 228
> 9 182 206 230
>
> A given number is 204 in cell E1, I would like to determine the value 
> under
> column A, which should return 7, because 204 is under the same row.
>
> A given number is 174 in cell E1, I would like to determine the value 
> under
> column A, which should return 1, because 174 is under the same row.
>
> Does anyone have any suggestions on how to do it in excel?
> Thanks in advance for any suggestions
> Eric 


0
T
6/5/2010 3:35:31 AM
Reply:

Similar Artilces:

Disappearing Contacts 04-23-10
I'm running Windows Live Mail, and some of my "contacts" randomly disappear after a few days...There is not any pattern I can discern, although some are repeaters. I've entered them both through "add contact" after receiving an email, and by direct setup or entry under "new". Any suggestions to correct? Never had this problem with Outlook Express. See if this applies Update to the Windows Live Communications Platform Lost Contacts bug - Contacts.msi http://messengergeek.spaces.live.com/blog/cns!E3785B1281BBDA1!4771.entry -- Mike - ...

Scatter plot
I have an XY scatter graph with some points in it. My data in the spreadsheet looks like this: [Title] [X-value] [Y-value] [size] Data 1 | 10 | 10 | 1 Data 2 | 33 | 44 | 5 What I want to do is to change the size of the data point according to the value I specify. Right now, I have to individually modify each point and add a number corresponding to the size. Is there anyway to take the size-value from the spreadsheet and change the size of the point in the graph? In the long run, I am also looking to modify the color. If the solution requires some VBA coding, I am up for that. Any sugges...

Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...

Money 2003 #10
Long story short, I have my checking data on my Pocket PC only and had to re-install Money 2003 on my desktop. How do I get the data from the Pocket PC to the desktop? When I try to synchronize, it wants to install Money on my Pocket PC. I have it on my Pocket PC already, complete with my checking info. I am afraid it will erase my data. ...

Inventory Value
what even old programs like Cougar has, RMS needs to address. The true value of inventory based on either a LIFO or FIFO method. Year end inventories for tax purposes needs to be more acurate than "last Cost" or "Weighted Average". Average weighted cost is the accepted standard for retail. LIFO-FIFO is impractical for most retail businesses. "Marc Cotton" <Marc Cotton@discussions.microsoft.com> wrote in message news:0BC94A17-6BB8-4DA6-A8DE-EBE91596BB6B@microsoft.com... > what even old programs like Cougar has, RMS needs to address. The true &...

Cell Values
I would like to return the value of another cell(C1) if a cell has a value in it, otherwise I want it to return a value of 0. Example: IF(A1="any value",C1,0) This formula would be written in B1. "Any value" would be numbers or text. Thanks 4 your help!!! Try: =IF(A1<>"",C1,0) or =IF(ISBLANK(A1),0,C1) Good Luck, Mark Graesser "GaryW" <gary.wicker@acadiapolymers.com> wrote in message news:040b01c34721$41fe8d00$a301280a@phx.gbl... > I would like to return the value of another cell(C1) if a > cell has a value in it, otherwise I...

Calculate to another cell as default value
Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't Why not use an extra column (C) and have this formula in D: =IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "...

Money 2005: Shouldn't the 'Market Value' = 'Last Price' x 'Quanti
For Microsofts Money 2005 delux, shouldn't the 'Market Value' equal the 'Last Price' times the 'Quantity' in the 'Valuation' view of the 'Portfolio Manager', . For example, one line reads: "SYMBOL: ABC , CHART: [^], NAME: ABC Technology Inc., LAST PRICE: 32.65, QUANTITY: 20.000, COST BASIS: 432.00, MARKET VALUE: 15,724.18, INCOME: , PRICE APPRECIATION: 221.00" As you can see, all of the calculations, except the "MARKET VALUE" are correct. Even the PRICE APPRECIATION which depends on the value of the "MARKET VALUE&...

Days in month for 10 year period
What is the quickest way to get the days in a month in a series. Eg 31 Jan 2005 28 February 2005 Put this in A1 =DATE(2005,ROW()+1,0) And drag down 120 rows. Format the cell as: dd mmmm yyyy Geoff wrote: > > What is the quickest way to get the days in a month in a series. Eg > > 31 Jan 2005 > 28 February 2005 -- Dave Peterson bit unclear myself, but maybe format as ddd dd mmm yyyy -- HTH RP (remove nothere from the email address if mailing direct) "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message news:427B64F1.622B2742@netscapeXSP...

How to take master page control value in .aspx page
Dear friends, Please tell me how to take master page control value in .aspx page. I have a master page name master1.master, which holds one drop down <asp:DropDownList ID="ddlName" runat="server"> <asp:ListItem>Vinod</asp:ListItem> <asp:ListItem>Manoj</asp:ListItem> <asp:ListItem>Alok</asp:ListItem> <asp:ListItem>Amit</asp:ListItem> <asp:ListItem>Osho</asp:ListItem> <asp:ListItem>Ganesh&l...

Error 02-07-05
Guys , I have this error : "Please verify that you have enough Crystal licenses. " when ever i try to open any report. I have installed CRM using Adventure Works Cycle license and import all sample data. Can any body help me how i remove this error. Regards, Syed Fahad Anwar. Hi Fahad! "Fahad Anwar" schrieb > I have this error : "Please verify that you have enough Crystal > licenses. " when ever i try to open any report. This is a common problem, but it can be caused by a number of issues. Search this group: http://group...

GP 10 payroll direct deposit
In processing this first payroll in 2010, I am getting an exception on the print checks screen when I process the direct deposit. Everything went ok through the build and calculate but when I process the direct deposit it gives me an exception report on 3 employees. Exception #2 - amount deducted but not deposited. These 3 are all on total direct deposit so it not a linked deduction. Everything I find in techknowledge suggests it is a link or sequence problem which would have to be a deduction deposit. All the other employees are processing correctly so it is just 3 out of a...

Send/Recieve hanging at 3 to 10% and doesn't finish
Two of my co-workers are having a problem with Outlook. When performing a send/recieve it just hangs at 3 to 10% status and just stays there. If they leave it for an hour eventually it will finish. Is there a setting that is causing this? What could it be? Help?? Information please. What version of Outlook? What type of Account is being used? What kind of bandwidth do the users have access to? Are they sending attachments? If yes, how big? What do you have set for your server timeout if you are using ISP mail? If you are using Exchange, what version? >-----Original Messag...

Default value and font colours?
Hi, this is probably something really simple. I have a drop-down box that gets its selections from a table, all I want to do is set it up so that it automatically comes up with one of the items like a default value unless someone wants to change it, I had a look at the default value option in properties but cant work out how to direct it to an actual entry in a table? Also if you have a combo box that shows selections is it possible to make just one of the selections a different colour font from the others? Thank You for all your help! Jay -- Message posted via http://www.accessmonster.co...

CRM E-mail 05-18-04
We installed SFO on an XP workstation. 1. The e-mails do not link with CRM Activities. We used Send CRM e-mail and Promote e-mail to CRM. 2. When We go offline we get a error message "unknown error. try again or contact system administrator. We can see the accounts from the Outlook CRM task bar. Is the issue with the E-mail Router? Thanks Hi Mike, to 1. - after you promote a email, you have manually add to the activity to 2. - can you start the sql agent with the right sql server from the SfO client - does any buty else hase the same problem, if not check the difference of right...

Help Exporting DATE values
I have a table that has 5000 dates in the "date" column. I need the date exported to a comma delimited file in this format... March 10, 2007 But the date is exported like this in the text file... 3/10/2007 0:00:00 How can I fix this? By the way, changing the format of the date field in the design view of the table doesn't seem to fix this. Help! Thanks Hi Sincity Write a query making the date field as follows dateexport: Format([date],"mmmm d"", ""yyyy") then export the query Regards Ian B "SinCity" wrote: > I have a ...

How to exclude conditional calculated no or zero values from a cha
Picture this: a chart for 30 days and 30 values. The values come from a different table (sheet1!A:G) using the following formula =IF(VLOOKUP(J11;Sheet1!A:G;2;FALSE)="";"";VLOOKUP(J11;Sheet1!A:G;2;FALSE)). If the table on sheet1 has no value (""), then the a.m. formula also displays no value (""). The chart however does show a zero value, which i do not need. Is there a possibility to not show this zero or no value in a chart? Thanks for your help! Hi, Use the formula NA() instead of "". Here are a couple of pages on the subject. http...

Value Y axis
I have a chart with 5 columns in the data range from (typically) the following data. The data range is already set up in a template so the user pastes in values to get the correct chart. Because the data changes with each instance the source data can vary, hence some cells are empty. 0.2 0.0017497 0.0017397 0.001774494 0.001704906 0.4 0.0017613 0.0017397 0.001774494 0.001704906 0.6 0.0017538 0.0017397 0.001774494 0.001704906 0.8 0.0017459 0.0017397 0.001774494 0.001704906 1 0.0017397 0.0017397 0.001774494 0.001704906 1.2 0.0017358 0.0017397 0.001774494 0.001704906 1.6 0.0019809 0.0...

Subform comparison of 2 tables excluding non zero value
I am creating a subform (Access 2003/NT) in which the user will type in a Melt# (contained in the tblChemicalResults). I need to create a side by side comparison of the chemical requirements from the tblChemicalRequirements to the actual results contained in the tblChemicalResults, but I need it to exclude any non zero values from both tables. Each table contains all of the possible 14 different metal elements that could possibly go into making a particular part. Obviously, not all 14 go into every part, so how do I display only the elements that have a non zero value while also display...

Blank forms 01-06-10
Hi, I recently took over our Access 2003 Database from our guy that got laid off and I am a newbie++. I am having a problem with a form. I have a form then I have a subform embedded within that. When I log in with the developer account, I can see the form, but the area where the sub form is is greyed out. When I log in as a user and try to open the form, I have a completely blank window. When I am looged in as developer and look at the form in Design view can I can see all of the fields in the subform that are supposed to be there, but in Form view it magically becomes grey. I ha...

Missing values from a pivot table field
I am creating a pivot table from an access database containing 896426 records. The issue I am running into is that on one of the fields the pivot table is only pulling in one of two values, but the access table does contain the second value. When I click on the arrow associated with that field it does not contain the second value either. Is there somewhere else in the settings this value could have been lost? Perhaps try Drag the field off the table Refresh the table Drag the field back on. With pivot tables you need to let us know which XL version you have. pivot ta...

More about application domain 05-17-10
Hi! If I want to have 5 application domain and in each one have the assembly test1.exe and test2.exe be executing. the only way to do this is to have 5*2 = 10 threads be running. This must be correct ? Now to one more question when you run two assemblies test1.exe and test2.exe in th esame application domain these two might interfere with each other resources because they might share some resources. So here we might have to use some kind of synchronization to shared resources. This must also be correct ?. You don't have to worry about synchronization when you run exe...

SFO problems 08-16-05
Hi all! Im desperate looking for a solution! Previous month I have installed MS CRM! When I went on my vacation all worked fine. Now I'm back and on a mysterious way 3 of my users have problems, but all have different ones. I know the problems are caused bij the SFO because when I try it with the IE all works fine. The first problem is that when the user select contacts/accounts/etc in Outlook the error is prompted "page cannot be found". This error isn't solved by restarting Outlook or a ISSreset. The second problems is when an other users wants to create a new con...

Copy to specified sheet, values only
Good morning I hope someone can help me with this. I am currently using the following code to copy the entire data from one sheet, and paste it below the previous data in a different sheet: Set srcsht = Sheets("Working") Set dstsht = Sheets("All Trades") LastrowA = srcsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row LastrowB = dstsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1 srcsht.Range("A1:A" & LastrowA).EntireRow.Copy dstsht.Cells(LastrowB, 1) However, I need the macro to only paste the values, not the formulas, data...

Office 2004 ST and Mac OS X 10.4 Tiger
With Apple's announce to release Tiger on the 29th, does anyone know of 'ol Bill's plans to release an update? I'd love to know before going out and getting Tiger. Thanks! -chuck Look here: http://www.macworld.com/news/2005/01/11/microsoft/index.php and here: http://www.macworld.com/news/2005/04/12/mstiger/index.php Michel On 14.04.05 2:44, in article 1113443058.634159.101470@l41g2000cwc.googlegroups.com, "chuck" <carlos.s.castillo@us.army.mil> wrote: > With Apple's announce to release Tiger on the 29th, does anyone know of > 'ol Bill's...