# 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)

ADVERTISEMENT

## MATCH Function Returns Unexpected Result

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.

## Function To Indicate Whether An Expression Returns A Non-zero Result (2007)

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!

## If INDEX,MATCH Function Returns 0, Can I Change It To Use Value From Adjacent Cell?

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:

## Index Function With Multiple Ifs Won't Return More Than First Result

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.

## Index And Match Function Not Working?

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)

## Command Button Not Working Correctly?

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"

## Filtering By Dates Not Working Correctly?

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.

## Auto Sort Not Working Correctly

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

## Adding Multiple Times Together Not Working Correctly

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

## Selection Change No Longer Working Correctly

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?

## Why Formula Returns Result For One Line And Not The Other?

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.

## VLOOKUP (LEFT) Returns Result Off By One Row

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.

## Sum Formula Returns Incorrect Result

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,

## Index Match Several Returns.

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.

## How To Correctly Create OR Function

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?

## IF Statement: Just Returns FALSE, Despite The Result Being True

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.

## Formula Returns A Result But The Cell Displays A Zero

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.

## Vlookup Returns Data From One Row Above The Desired Result

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.

## Increase In Decimals Returns Strange Result

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?

## INDEX Returns 0 When Cell Not Populated

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.

## Index/Match & Sum: Returns The First Number

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,

## Countifs Function Not Summing Correctly

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]....

## Formula That Returns A Result Based On All Dates In Column A

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

## INDEX Returns On Rows - To Return In Columns

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)

## Listbox Selection To Range Will Not Function Correctly?

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]....

## Formula That Returns Result & Font Color Of Referenced Cell

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)

## Calculate Monthly & Annual Returns Of Security Or Index

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,

## Function Returns Value To Cell As Single - Function Is Defined To Return Double

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]....

## Matrix To Return Risk Rating - Array Returns Opposite Result

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.

Copyrights 2005-15 www.BigResource.com, All rights reserved