# Index Function Working Correctly When Hit F9 But Returns Different Result

Aug 22, 2012
I'm compiling a list of names on a separate tab from a column that only lists the names sporadically. Problem: In the formula, the IF provides a TRUE, the row function, and consequently, the INDEX function (when I hit F9) provides the correct "name" and row number, but when I hit return, it provides the text 4 cells beneath what it should.

=IF(ROWS(F$5:F5)

View 7 Replies
ADVERTISEMENT
Dec 3, 2013

In the attached file I used =MATCH(TRUE,INDEX($B$2:$B$10="",0)) to retrieve the location of the first empty cell.

When using the "Evaluate Formula" tool, it is clear that the position in the array created by the INDEX function is the 6th. Nevertheless, the final outcome is 9, being the last cell in range.

View 3 Replies
View Related
Jan 28, 2010

I have this COUNTIFS expression buried in a larger formula:

COUNTIFS(Table[C7],Table[C7]&"",Table[C21],">=1")

and it always returns a value of zero or some positive integer.

I'm now looking for a little "function" that I can wrap around this expression that will:

A) indicate any non-zero result as 1 or TRUE

and

B) indicate any zero result as 0 or FALSE

Something like...

AREYOUNONZERO( (COUNTIFS(Table[C7],Table[C7]&"",Table[C21],">=1") )

I'm sure there's a fairly efficient way to do this... but I'm totally stumped at the moment!

View 7 Replies
View Related
Mar 16, 2009

If a INDEX,MATCH function returns a zero, how do I get it to use the value in the adjacent cell which is a unique number?

=INDEX(Sheet2!A:C,MATCH(A2,Sheet2!A:A,0),3)

Example:

View 2 Replies
View Related
Aug 11, 2014

I am attempting to get an INDEX function with multiple "IF" statements to return more than one result. Building multiple IF statements. That portion of the below formula works. However, I can only get the INDEX formula to return the first, single result.

Formula:

=IFERROR(INDEX(Func_Area,SMALL(IF(Const_Start<=DATE(YEAR($B$2),MONTH($B$2),DAY($B$2)), IF(Cost_End>=DATE(YEAR($C$2),MONTH($C$2),DAY($C$2)),ROW(Func_Area)-MIN(ROW(Func_Area))+1,"")),ROWS($A$4:A4)))," ")

Func_Area,Const_Start,Cost_End are all Named Ranges on Sheet "Proposed". My intention is to return a list of projects on Sheet Test1 for all projects starting construction after a certain date and completing construction before a certain date. I suspect something in my ROWS fuction or ROW-MIN+1 is wrong.

This formula returns the first result in the first row, but every subsequent row is blank...see attached spreadsheet.

View 3 Replies
View Related
Sep 27, 2012

I am trying to use index & match function to extract data from data base with has multiple critera in rows as well as cloumns but it is not giving correct results.

Example:

A

B

C

D

E

[Code].....

In C9 I typed =INDEX($B$2:$E$6,MATCH($A9,$B$1:$E$1,0),MATCH($B9,$A$2:$A$6,0))

The result I got was #Ref!

what formula should I use in C9 to get 20, C10 to get 5 & so on

FB1 to FB4 are column headers (critera)

GL 1 to GL5 are row headers (critera)

View 2 Replies
View Related
May 1, 2013

I have a command button on my form that unloads it when i am finished. But i have to press it 20 times before it closes. The only line i have in the code is "UnLoad Me"

View 7 Replies
View Related
Jun 26, 2014

[Code] ....

In column F I have a list of dates (dd/mm/yyyy).

I can manually filter dates ok, but want to set it to part of code to use automation for a report.

The code when run, finds nothing, where it should. I tried using [CDate] also but also same result.

View 3 Replies
View Related
May 12, 2006

I'm try to get the pos. placing in a auto sorted table to remain in place

i.e. 1st next to the top position. 2nd next tothe next position and so on down. but it keeps getting it wrong! I've tried amending the macro serval times but it makes things worse! Sorry can't attach the file, too big for this forum!!!!

View 2 Replies
View Related
Jun 10, 2013

I have a list a time (HH:MM:SS) that i want to sum together to get the total number of minutes.

It works if I use simply add A2+A3+A4 etc, but not when I use sum(a2:a4).

There are over 2500 lines of data.

Sheet attached : times.xlsx

View 3 Replies
View Related
Apr 11, 2007

This vba code is supposed to move the cursor to the Cacluate1 command button when the user clicked on cell H6.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$H$6" Then Calculate1.Activate

End Sub

The only changes that I have made is a change to a private sub worksheet_change(ByVal TArget as Range). Would these changes have anything to do with this no longer working?

View 9 Replies
View Related
Jul 27, 2014

I have a two different formulas the return a numbered result(PO Number) in the same column. I then vlookup both of them with the same formula into a pivot table, one returns the result one doesn't. format appears to be the same.

View 6 Replies
View Related
Jan 31, 2014

Code: =VLOOKUP((LEFT(C4,6)),'Data from 7500'!$B$16:$G$195,6,TRUE)

And it works great, except that the data returned is off by one row. For example, the correct value for the sample name in B107 is located in G107, but the formula returns the value in cell G106. I've tried changing the TRUE to FALSE and that returns #N/A.

View 5 Replies
View Related
Dec 1, 2009

I have created a table in excel, and it has a very simple sum totals. When i include all cells in the table the total becomes 0. When I exclude a cell in the table, the total shows. I have deleted the row and re inserted another one and copied the cell content from above that was not having a problem,

View 9 Replies
View Related
Dec 24, 2007

agentsIDCount Name1ACCT1Name2ACCT2

Joe1113

Jimmy2222

Sarah3333

The above is A1 thru G1.

I have a list of accounts on Sheet2 that have the agent ID's (listed on K:K), Acct #s listed D:D and names E:E.

I want to match Sheet1 B2 with Sheet2 K$1$:K$50000$. I want to return the first match under this ID from Sheet2 Column E (to post on 1 Name on sheet 1) and then Column D (to post on 1 Acct# on sheet 1).

Then the next match goes to 2 name, 2 acct # and so on.

View 9 Replies
View Related
Mar 3, 2014

I should i make this formula

A customer is eligible for a discount if the customerâ€™s 2009 sales are greater than or equal to 200000 OR if the customers First Order was placed in 2012. " If its true it would be yes and if false no"

So far i have in the IF function

=IF([@[2009 sales]]>= 200000 ,"Y","N")

How should i insert if the customers OR First Order was placed in 2012?

View 2 Replies
View Related
Dec 29, 2008

I have a worksheet called "Raw Data" where in columns J, I and G contain values. I would like to write a formula whereby if all 3 conditions are met, it will count the number of values found in column C.

I've tried to write an IF statement but it just returns FALSE, despite the result being true. Need the right combination of IF/AND statements that would do this?

=IF(AND('Raw Data'!$J:$J="Maintenance",'Raw Data'!$I:$I="Open",'Raw Data'!$G:$G="1-2008"),COUNT('Raw Data'!$C:$C)). I've tried to attach the workbook, but there's a problem with uploading attachments I think. Sorry if my explanation is unclear.

View 4 Replies
View Related
Jan 18, 2006

I have a simple formula subtracting one cell from another using =SUM(XX,-XX). When I click on fx and bring up the function arguments box, the formula result is displayed correctly in that box. However, the cell containing the formula will only display a zero. I have tried reformatting the cells to no avail. I have also tried getting a result using =XX-XX and that does not work either.

View 10 Replies
View Related
Mar 18, 2007

I have a three or four letter abbreviation of a last name in A1. Column C contains a list of Last Names; column D contains a list of corresponding First Names; and column E contains a formula concantenating the First Names and Last Names from columns C & D.

I have the following formula in B1:

=VLOOKUP(A1,C1:E44,3,1)

I am attempting to match up the three or four letter abbreviation with the concatenated list to return the full name (First and Last) in cell B1, however, I'm not getting the desire result. I keep getting the name in the row just above the name I want.

View 9 Replies
View Related
Jul 21, 2006

I have discovered a strange thing about excel and it is driving me mad and ruining my project.

Basically, If i enter the number 97.4 into cell A1 and then 97.165 into cell B1 and then enter the formula =A1-B1 into cell C1 i get the answer 0.235. Which is correct.

However, when you increase the number of decimal places to 30 the answer that is returned is 0.234999999999999000000000000000.

This calculation result seem to fluctuate wildly depending on the primary numbers in cells a1 and b1. Very rarely have i seen the full 30 decimal places without a bizarre result like this.

Maybe it has been too many years since i was at school and that i am confused with my numbers and that there is a real easy answer to why this result is returned.

As i said it is ruining my project that i am working on. Can anyone help? Why does excel do this? Is there a way to stop this happening? My project relies on this answer to be correct. Is there a way to force excel to stop doing this?

View 9 Replies
View Related
Mar 28, 2014

See attached formula:

"=INDEX(DATABASE.xls.xlsx!$AT$5:$AT$2001,MATCH(AR10,DATABASE.xls.xlsx!$O$5:$O$2001,0))"

Cell AR10 is my target cell and then there are 15 other cells that populate when the correct information is put into cell AR10, but if one of the other cells does not have any information in it, it returns a "0" (zero). I am trying to get the cell to show blank as when I print I don't want a "0" (zero) in every cell that has not got any information.

2 x cells are showing telephone numbers, 1 x landline and 1 x mobile - sometimes we don't have a landline number, so I want the cell to show blank not "0" (zero)

The cell format is set to "text", but I have tried setting it to "general", "number" and finally settled on "text", but all return "0" (zero) if we don't have a number for them.

View 6 Replies
View Related
Aug 7, 2009

I have the following formula that returns a number, however it returns the first number it comes to when the criteria is met,

View 2 Replies
View Related
Jul 13, 2012

I have yet run into another road block, which is stated below.

=SUM(COUNTIFS(AF20:AF111,{"*CoSale**","*Reo**"},AL20:AL111,{"Seller - Core","Seller - Non Core"}))

I can not get that formula to sum correctly. I can get them to sum if I seperate, but not with the countifs formula above. I am stuck at this point. So, any insight would be great. It is supposed to add up to 13, but I am getting a sum of 7.

REO-Svcr

Seller - Core

CoSale-Pri

Seller - Core

[Code]....

View 3 Replies
View Related
Feb 15, 2007

I need a formula to count cells based on the date, so that I can have a blank cell when the answer is 0. I am adding values cumulative and future cells need to be blank because I have a graph that has a trend line and I don't want the trend line to fall off at the end. I also don't want to have to go back to this every month and update it.

Column A

Date

1/2/2007 0:00

1/3/2007 0:00

1/10/2007 0:00

2/10/2007 0:00

2/10/2007 0:00

2/15/2007 0:00

3/22/2007 0:00

3/22/2007 0:00

3/22/2007 0:00

4/31/2007 0:00

4/31/2007 0:00

4/31/2007 0:00

Column B

Invoice #

CM-0003881

CM-0003882

CM-0003883

CM-0003888

CM-0003928

CM-0003932

CM-0003933

CM-0003985

CM-0004007

CM-0004008

CM-0004009

CM-0004065

I have this formula in cell F3 that will add the dates through the end of January:

=SUMPRODUCT((A1:A1000>=DATEVALUE("1/1/2007"))*(A1:A1000

View 10 Replies
View Related
Aug 18, 2008

I'm using the following to return multiple reponses to a criteria. However, it returns the answers in rows. i.e. A1, A2, A3. I want it to return in columns. i.e. A1, B1, C1.

=INDEX(RMList!$A$1:$B$32381,SMALL(IF(RMList!$A$1:$B$32381=Errors!$D$2,ROW(RMList!$A$1:$B$32381)),ROW(1:1)),2)

View 9 Replies
View Related
Jul 2, 2013

I am using a multi-column listbox on a worksheet. When I select an item from the listbox, the value from the listbox first column should populate the first empty cell in a range of cells L:16 to L:25. This should be easy but I cannot get it to function. Below is the code I used prior to modifications I made to the worksheet.

VB:

Private Sub ListBox1_click() 'allows user to select item from list box and paste to datagrid

Dim lngLastRow As Long

Dim lngcol As Long

[Code]....

View 6 Replies
View Related
Apr 3, 2008

I have 2 tables, 1 beneath the other - table 1 columns represent date ranges and their values. Columns and their data are alternating color coding. Table 2 references, by means of array formula, this data - IS it possible to include the font color as a result?

BCDEFGH503/0310/0317/0324/03609/0316/0323/0330/03712348Prod113,91113,69713,58213,4849Prod21,9241,8943,1151,86510Prod31,2601,2401,2301,22121Age (Days)22Prod1211391123Prod22624Prod326

Formula in H22 = '{=SUMPRODUCT(((H$5-$D22)>=$E$5:$O$5)*((H$5-$D22)

View 9 Replies
View Related
Jan 3, 2007

I feel pretty dumb asking a basic math question but I couldn't find the answer anywhere. I would like to calculate the monthly & annual returns of a security or index. When trying to calculate various indexes to insure my math was correct, my numbers never match that of yahoo or bloomberg's. If you could just start me down the path,

View 4 Replies
View Related
May 14, 2013

Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?

Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double

Dim temp As Double

[Code]....

View 9 Replies
View Related
Nov 20, 2013

I am looking up a risk matrix to return the risk rating... it is very simple, it works on one sheet, but on a different sheet (looking up different data, but same basic format) it returns exactly the opposite rating.

My formula is: =INDEX($G$6:$K$10,MATCH(I19,$G$5:$K$5,0),MATCH(J19,$F$6:$F$10,0))

Here is F4 to K10

I have data validation on I19 and J19 based on the cells below

Consequence

Priority

Very Low

Low

Medium

High

Very High

[Code] ..........

BUT... when I put Very Low and Very Low in the two cells (I19 and K19) I get Very High as the return.

View 1 Replies
View Related