Cant get index match formula to work

[mdot218]

Hi All

I cant seem to get an index match formula to work.

I have got a data input sheet, and a monthly report sheet which pulls data from the input sheet. On the input sheet, there are multiple rows with the same date.

On the input sheet cell A3 has date "31/12/2013" and cell B3 has value "8". Cell A4 also has date "31/12/13" and cell B4 has value "1". Now, on the report sheet, the problem I have is that I cant get B4 value "1". Instead it returns the B3 value "8".

The forumla I have used is: =IFERROR(INDEX(guns,MATCH(A3,date,0)),"") it is an array formula, cant do the correct parenthatsis.

what am I doing wrong?

thanks

[JBeaucaire]

I see people struggle with stuff like this, trying to get a second sheet to display a subset of data from a first sheet, and the second sheet is literally an exact duplicate of the first in terms of layout. IN this scenario, I wonder why the second sheet, and thus the problem, is needed at all.

If all you want is to see a full representation of the same data in a smaller subset, do that on the original sheet using an Autofilter and all these struggles go away.


1) Delete the Monthly Report sheet
2) Rename the "input" sheet to "Data", cause that's what it is.
3) Click on row 1 and turn on the Data > Filter
4) Use the drop down in column A to apply a filter to show all of December 2013 only
5) Print, edit the visible rows as needed, then do a SHOW ALL on column A to see all data again.


The Autofilter was invented for the need you're demonstrating.

[Pepe Le Mokko]

Eevntually enter

Please Login or Register  to view this content.

in B3, confirm with CSE and pull down as neede

[Jonmo1]

The Autofilter was invented for the need you're demonstrating.

Pivot Tables as well.

[mdot218]

Thanks for all the responses. I thought of auto filter, but wanted a report sheet as there will be more info displayed there. I dont know enough about pivot tables to attempt them yet.

With regards to the formula, I have used it my example spreadsheet, and it works perfectly. However, in my real spreadsheet, it doesnt work as well. It manages to pull some data from the input page, and in some cells, it returns a blank. (it did return a #num erro, but I started the formula with iferror)

any more suggestions?

thanks

[Pepe Le Mokko]

Without seeing the real data, difficult to say

[AZ-XL]

Hi
What about this array formula. paste to b3 cell and drag down
=INDEX(input!B:B,SMALL(IF(A3=input!$A$3:$A$23,ROW(input!$A$3:$A$23),""),COUNTIF('monthly report'!$A$3:A3,'monthly report'!A3)))

[mdot218]

Hi
thanks for response
it works well in the example, but not on my real spreadsheet.
Can i attach my real one or will that go against the forum rules?

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mdot218]

Hi

attached is a mock sheet which hopefully explains what i am trying to achieve.

Thanks

[martindwilson]

or like this

[AZ-XL]

b5 cell
=IFERROR(INDEX(DATA!$B$5:$F$20,SMALL(
IF(IF(MONTH(DATA!$B$5:$B$20)='REPORT - BEFORE'!$E$2,DATA!$A$5:$A$20,0),IF(MONTH(DATA!$B$5:$B$20)='REPORT - BEFORE'!$E$2,DATA!$A$5:$A$20,0),""),'REPORT - BEFORE'!$A5),MATCH('REPORT - BEFORE'!B$4,DATA!$B$4:$F$4,0)),"") CSE

[martindwilson]

using your workbook

[mdot218]

That works a treat. Very much appreicated. thanks

[arlu1201]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.