result of formula in cell instead of formula itself...

I've got a spreadsheet where I need to search a table and return a
value from a particular column in that table. Vlookup appears to be
exactly what I'm looking for, however the first argument I need to
pass it (the value to search for) is located in cell N2. Cell N2 is
itself a formula and it appears that it is making vlookup fail. Is
there a way I can tell the vlookup function to only use the result of
the formula in that cell instead of following the formula itself?

Thanks

--cfortran
0
cfortran (3)
3/20/2008 8:05:22 PM
excel 39879 articles. 2 followers. Follow

3 Replies
528 Views

Similar Articles

[PageSpeed] 17

On Mar 20, 3:05=A0pm, cfortran <cfort...@gmail.com> wrote:
> I've got a spreadsheet where I need to search a table and return a
> value from a particular column in that table. Vlookup appears to be
> exactly what I'm looking for, however the first argument I need to
> pass it (the value to search for) is located in cell N2. Cell N2 is
> itself a formula and it appears that it is making vlookup fail. Is
> there a way I can tell the vlookup function to only use the result of
> the formula in that cell instead of following the formula itself?
>
> Thanks
>
> --cfortran

I apologize for replying to my own post like this, but I think I may
have misunderstood the way vlookup works. Apparently it will only
search the left column of a specified table.

Here is my function: =3Dvlookup(N2,A6:B6006,1,FALSE)

What I want it to do is search column B and return the result for that
row in column A.

Any help will be greatly appreciated.

--cfortran
0
cfortran (3)
3/20/2008 8:12:17 PM
You can use =index(match())

=index(a:a,match(n2,b:b,0))

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html  (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html  (for =index(match()))

cfortran wrote:
> 
> On Mar 20, 3:05 pm, cfortran <cfort...@gmail.com> wrote:
> > I've got a spreadsheet where I need to search a table and return a
> > value from a particular column in that table. Vlookup appears to be
> > exactly what I'm looking for, however the first argument I need to
> > pass it (the value to search for) is located in cell N2. Cell N2 is
> > itself a formula and it appears that it is making vlookup fail. Is
> > there a way I can tell the vlookup function to only use the result of
> > the formula in that cell instead of following the formula itself?
> >
> > Thanks
> >
> > --cfortran
> 
> I apologize for replying to my own post like this, but I think I may
> have misunderstood the way vlookup works. Apparently it will only
> search the left column of a specified table.
> 
> Here is my function: =vlookup(N2,A6:B6006,1,FALSE)
> 
> What I want it to do is search column B and return the result for that
> row in column A.
> 
> Any help will be greatly appreciated.
> 
> --cfortran

-- 

Dave Peterson
0
petersod (12004)
3/20/2008 8:55:59 PM
=vlookup(N2,$A$6:$B$6006,2,FALSE) 

N2 will hold a value that can be found in A6:A6006 and return the appropriate
value from B6:B6006


Gord Dibben  MS Excel MVP

On Thu, 20 Mar 2008 13:12:17 -0700 (PDT), cfortran <cfortran@gmail.com> wrote:

>=vlookup(N2,A6:B6006,1,FALSE)

0
Gord
3/20/2008 9:16:07 PM
Reply:

Similar Artilces:

using a vb variable in an excel formula
I have a VB macro variable that I want to reference in an Excel formul (faulty code below gives me a #NAME? error) . There has to be an eas way to do this but I haven't found it yet. Thanks in advance. Sub test() ' ' Dim ok As String Dim no As String String1 = "ok" String2 = "no" Range("A1").Select ActiveCell.FormulaR1C1 = "23" Range("B1").Select ActiveCell.FormulaR1C1 = "45" Range("A2").Select ActiveCell.FormulaR1C1 = "=IF(R[-1]C>20, String1, String2)" End Su -- Message posted from http://w...

How to highlight cell with value?
I'd like to bold any cell with a value greater than 0. The value is changed manually and will sometimes be changed manually back to 0. How is this done? Thanks, Brett Take a look at Conditional Formatting in XL Help. In article <c003b25a.0404022209.6287ee99@posting.google.com>, account@cygen.com (Brett) wrote: > I'd like to bold any cell with a value greater than 0. The value is > changed manually and will sometimes be changed manually back to 0. > How is this done? > > Thanks, > Brett Use Conditional Formatting, under Format. Then set the condition ...

Cell Protection #4
Having problem protecting cells in shared folder on shared drive. Need to protect a group of cells that are for viewing only, but leave adjacent cells unprotected. Being a shared worksheet I believe is the problem, there are limitations. Any suggestions. Thanks to all. Susan I think you'll have to do all your setup (disabling protection/locking & unlocking cells/reenabling protection) against a workbook that isn't shared. But I'm not sure if that addresses what your problem is????? Susan wrote: > > Having problem protecting cells in shared folder on shared > dr...

Please help me decipher the result of COUNT.
Following data from A1:A7 Sales 12/8/2008 19 22.24 TRUE #DIV/0! =COUNT(A1:A7,2) returns 4. I don't understand where the four occurrences of 2 come from. 22.24 = 3 occurrences 12/8/2008 = 2 occurrences total of 5?? I know I am missing something. Please tell me. Is there a tool that I can use to trace? I tried the formula auditing toolbar, no luck because there is no error and trace precedents is not detailed enough. Appreciate explanation. Epinn have a look in the help index for COUNT -- Don Guillett SalesAid Software dguillett1@austin.rr...

If statement result appearing in different colors
Is there a way to write an IF statement where the text will change color depending on the result? eg =if(x=0,"RED-colored text","BLACK-colored text") where 'RED-colored text' would appear in RED, etc Thank You! WayneD Take a look at Format|Conditional formatting. WGD wrote: > > Is there a way to write an IF statement where the text will change color > depending on the result? eg > > =if(x=0,"RED-colored text","BLACK-colored text") where 'RED-colored text' > would appear in RED, etc > > Thank ...

Conditional Formatting using result of Cell Formulas
Hi, I'm trying to make a conditional format when the result of a formula results in a value being entered in a cell. I'm using Excel Xp and my formula is as follows. =IF(F160="Need Info",TODAY()-S160,"") I've tried using formulas like >0, <>"", <>BLANK etc. but they all seem to fail. All cells are formatted, even entirely blank cells, as if the condition has been met. BTW, is this formula better strategically than the above formula. =IF(F160<>"Need Info","",TODAY()-S160) Just curious. TIA!!! Norm If ...

VBA Formula Problem
I am using the code below and get an error with the formula statement. I need help. TIA Sub Salary() Range("Salary").ClearContents Selection.QueryTable.Refresh BackgroundQuery:=False For Each c In Range("LName") c.Formula = Upper(Left("D2", Find(",", "D2", 1) - 1)) Next End Sub Greg the formula needs to be a string. D2 should not be in quotes either: c.Formula = "=Upper(Left(D2, Find("","", D2, 1) - 1))" Note that this will set the entire range to read from D2. If you want it instead to read from column...

Simple cell prog. with IF...ELSE
Hello XL Gurus! I need to warn you - I'm a perfect newbie! :confused: Well, this is what i would like to do in an Excel sheet: I have made some kind of table, and in some of the columns I need t make some calculations *depending * on some inputs in some of the othe columns. For instance, a price calculation: I need to calculate some averages, to sum some cells...(that ain't problem), but then I need the calculation to add 15% in case that th value one of my cell is (for example) VAT. Let me ilustrate naivly: G2 = (if C2='VAT' then (A1*B1)+((A1*B1)*0.15)else(A1*B1)) ...

new line within a cell
Hi In Excel 2002 or 2007, I need to enter several lines into a single cell. What I mean is I need the capability to tell Excel to enter the next line of text as a new line within that cell. Is that possible? Obviously Enter does not work because it accepts the entry and goes to the next cell. Thanks. Jeff Type some text in a cell Alt + Enter Type some more text in same cell Alt + Enter Type a third line in the cell Enter to leave cell. Gord Dibben MS Excel MVP On Fri, 22 Oct 2010 11:27:11 -0400, "Jeff@nospam.invalid" <Jeff@nospam.invalid> wrote: >Hi &g...

charting missing data (empty cells bug)
Hi I'm trying to chart data with some missing points. I read posts on this forum on this topic and checked with my settings but they seem to be OK. Still, my empty cells are plotted as zeros. Only one (!) cell out of 5 is not plotted(and I failed to figure out how that one was different from the rest). Here are the current settings: - Excel version is French. - Cells are not hidden, but protected. - They are customized at "empty cell" format (;;;) - Empty cells are set to be plotted as gaps. Your help would be very much appreciated. EggHeadCafe - Software Developer Portal...

Display Formula Problem
All, I have search Microsoft's Knowledge Base, but could not find an answer. Hopefully, someone here can solve my problem. I am using Excel 2002. I have several project managers maintaining budgets in Excel workbooks. I am creating a summary Excel workbook. To get the data out of the workbooks, I start with entering an equal sign, click on the cell in one of the other workbooks, and press enter. The value from the source workbook is displayed correctly. Since I want to capture several columns and rows around this first cell, I want to copy formula in the first cell into near cells, I mu...

TEXT IN CELL, but not too wide
I wish to type a paragraph into my cell. Instead of having it appear several inches wide on the spreadsheet, I wish it to wrap, say, into 4 or 5 lines, each line just a few inches wide. How is this done please?? Thanks, FLKulchar Type a first line, then Alt-Enter, another line, Alt-Enter, etc. -- HTH RP (remove nothere from the email address if mailing direct) "FLKULCHAR" <FLKULCHAR@discussions.microsoft.com> wrote in message news:4D7F76AB-FA58-46DD-9E5C-02BC5F4F65BA@microsoft.com... > I wish to type a paragraph into my cell. > > Instead of having it appear...

Blank cells not to compute
I have cells that say if value is equal a certain points will be given if a tie 2 points are given by comparing the score in 2 cells. the problem the games have not been played and is already showing a tie so everybody is gotten their 2 points for the tie and 5 point for getting the score correct blank = blank. How do i do so the cell when they are blank do not compute =IF(COUNT(A$1:B$1)<>2,0,IF(AND(A2=A$1,B2=B$1),5,0)+IF(A2=B2,2,0)) -- David Biddulph "victorio" <victorio@discussions.microsoft.com> wrote in message news:A5EA003B-2329-457D-9A9D-9ACC7...

Event Macro
I am trying to use an event macro referenced in another thread, Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True 'Eliminate Edit status due to doubleclick Target.Offset(1).EntireRow.Insert Target.EntireRow.Copy target.Offset(1).EntireRow Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents End Sub (credit to: http://www.mvps.org/dmcritchie/excel/insrtrow.htm) but am getting a debug message on the following line: Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents I need clari...

IIF formula
I have a formula in a query which works fine until I tried to test it by deleting values in the form. Instead of nothing showing up, like how the formula should work, it showed ",X" The formula Other Fills: IIf([Other]=0,Null,[Other] & "X" & [Type]) & IIf([Other 2]=0,Null,"," & [Other 2] & "X" & [Type 2]) Example: [Other] = 2 [Type] = 4 [Other 2] = 3 [Type 2] = 5 Should look like 2X4,3X5 When [Other 2] and [Type 2] values are deleted it should look like 2X4 instead of 2X4,X The default value for the [Other] and [Other 2]...

How to setup formula to collect ongoing information
I have setup a questionnaire which will continuously have an answer section added to it. I am setting up a formula to count the yes and no answers. How would I set a formula up to count all the ongoing answers? Example would be =COUNTA(B6:G6) but then I add another two column of answers and need the formula to now read =COUNTA (B6:I6). Do I need to keep adding a new formula? Thank you. Hi, easiest way is using 'dynamic ranges' (http://www.contextures.com/xlNames01.html#Dynamic) HTH -- Pecoflyer Cheers -------------------------------------------------------------------...

which formula?
In column M, I record the results of appointmens (Sale, No sale, Cancelled etc) Everytime I input a result I'd like me Result summary to update automatically. I do it manually at the moment but it takes time and its easy o make an error. I've looked at SUMIF and thought I was close bu couldn't get it to work. I thought COUNT would do the trick but it ignores text. Any ideas? Damian You had the right idea... =COUNTIF(M1:M100,"No Sale") will give you the number of "No Sale" entries in the range M1:M100. Adjust this for your use. Thanks heaps, that ...

formula query #3
I have a group of 52 "weekly" spreadsheets , each one is named after this week's start date in a prcise format e.g. 130305.xls. I want to create a cell within each weekly sheet which simply contains this weeks date. Obviously I could manually enter this data but I would prefer it if Excel could automatically access the filename of this spreadsheet and embed it accordingly in the cell. Can anyone tell me if this is possible? TIA Wayne Chip Pearson has formulas for returning the Sheet Name or the File Name: http://www.cpearson.com/excel/excelF.htm#FileName Wayne K...

Protecting formulas #2
How can i protect a formula from either being edited or deleted? thx Hi Ed! If it's a single formula: Select all cells on the sheet by clicking the button at the intersection of Row and Column heads Format > Cells > Protection Remove the check from "Locked" OK Select the cell(s) to be protected Format > Cells > Protection Check "Locked" OK Tools > Protection > Protect Sheet Give and confirm a password (optional) OK If there's a possibility of sheet deletion to be taken into account, you must also use: Tools > Protection > Protect Workb...

sql result different to excel result
Hi, Just wondering why I am having such problems rounding figures, when I round the same figure on an Excel Sheet, I end up with a different end result to what appears in sql. The Excel Looks Like This. Net Vat Total Allocated Result (=(NET+VAT)/Allocated*Total) 900.00 157.50 1721.38 1721.38 1057.50 150.00 26.25 1721.38 1721.38 176.25 The Result field is a calculated field and is rounded to 2 decimal places, the calculation is in brackets next to the title name. The Table in SQL has the same structure but the net, vat,...

How to paste Conditional Formatting to other cells but using different data cell
I've made a spreadsheet that has a column for days of testing and another column that calculates the actual days it took for testing. Each row has a different day requirment of when testing should be complete. How can I format the column of "actual days of testing" to color the cell in red with bold type when the value of this cell is greater than the cell of "days of testing"? I tried conditional formating, but this only works for one row, when I tried to paste the format to other cells it used the data source from the first row. It would be too time consuming to indiv...

Count results from query with no results
I have continous form with an unbound text box [keyword] and a button [search]. There is also an unbound textbox [numberofresults] that counts the number of records returned. If the search produces no results, the [numberofresults] needs to read 0, instead it is just blank. What is the proper way to show zero results as 0 and not blank? It depends on how you are doing your search? Do you have a function? Are you just filtering the recordset? Dale -- email address is invalid Please reply to newsgroup only. "Ryan Tisserand" wrote: > I have continous form with an un...

Formula to return numerical, text result?
I need to create a formula that based upon three possible factors, wil return one of two calculations or a text message. If cell B="Good", the formula in cell C returns "Text" If cell B="Better", the formula in cell C returns cell A*.3 If cell B="Best", the formula in cell C returns cell A*.5 Thanks in advance for any tips -- burgeo ----------------------------------------------------------------------- burgeon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2023 View this thread: http://www.excelforum.com/showthread.php...

why cut and insert cells only works randomly?
It seems that cutting and inserting cells in the spreadsheet, errors every other time and locks the excell spread sheet... Hi Tony, You'll have to be more specific, but you might find the answers to such problems as inserting rows, using OFFSET with formulas. And you will probably find why extending formulas and does not work for you. -- if any of those are the problem. http://www.mvps.org/dmcritchie/excel/insrtrow.htm I don't know what you mean by locking the sheet, have you turned on sheet protection or have merged cells in your copy.. --- HTH, David McRitchie, Microsoft...

Updating cells using input boxes
Hi all My problem is that i am using Input boxes to update a spreadsheet. I have two coloums i want to update for two diffent type of information. I am currently trying to use Sub Compfees() MyInput = InputBox("Enter Students Name") Range("Sheet1!N20").End(xlDown).Offset(1, 0).Value = MyInput MyInput = InputBox("Enter Fee") Range("Sheet1!O20").End(xlDown).Offset(1, 0).Value = MyInput End Sub But this seems to update the first colum ok and move down updating as required, but it just replaces the value in the second coloum and doesn't move ...