Problem with my INDEX and MATCH formula

Hi,

On 'Sheet 1' D18 should equal G287 on 'Sheet 2'. However, it seems as though its pulling the data from G17. You will see the formula I have entered in D15 on 'Sheet 1'. Is it also possible to have nothing appear in the cell instead of "#N/A'? A space or '0' won't do due to formatting requirement.

Any help would be much appreciated.

Thanks,

Eoghan

=INDEX(Sheet2!$G$1:$G$292,MATCH(Sheet1!$C$5,IF(Sheet2!$D$1:$D$292=Sheet1!$B19,Sheet2!$A$1:$A$292)))

Because there will be added lines in the future is there a way to change the ranges on 'Sheet 2' to the whole column rather than the selected range?

i.e would this work:

=INDEX(Sheet2!$G:$G,MATCH(Sheet1!$C$5,IF(Sheet2!$D:$D=Sheet1!$B19,Sheet2!$A:$A$)))

D15 = =IFERROR(INDEX(Sheet2!$G$1:$G$292,MATCH(1,(Sheet2!$A$1:$A$292=Sheet1!$C$5)*(Sheet1!$B15=Sheet2!$D$1:$D$292),0)),"")
This is array formula

Thank you for your quick response.

I've just put this into cell D15 and it has given me '#NAME?'.

I am posting your file. It works actually

I must be doing something wrong!

I have copied your formula into my spreadsheet and pressed 'Ctrl+Shift+Enter'...

=IFERROR(INDEX(Sheet2!$G$1:$G$292,MATCH(1,(Sheet2!$A$1:$A$292=Sheet1!$C$5)*(Sheet1!$B19=Sheet2!$D$1:$D$292),0)),"") your typed formula
=IFERROR(INDEX(Sheet2!$G$1:$G$292,MATCH(1,(Sheet2!$A$1:$A$292=Sheet1!$C$5)*(Sheet1!$B15=Sheet2!$D$1:$D$292),0)),"") should be

you referenced b19 instead of b15. After enetring formula to D15 drag it down

I have made the changes you've suggested and the problem is still occuring

I checked your file. Its ok with cell references but you should press Ctrl+Shift+Enter for an array formula. In your last file you enetered as normal formula

I am pressing 'Ctrl+Shift+Enter' and its still not working...

Sorry about this, I appreciate your help!

Originally Posted by eoghanmolloy

I am pressing 'Ctrl+Shift+Enter' and its still not working...

Sorry about this, I appreciate your help!

Ok I uderstood the problem. You use excel 2003 but iferror formula does not function in 2003. Here is formula for 2003:
=IF(ISERROR(INDEX(Sheet2!$G$1:$G$292,MATCH(1,(Sheet2!$A$1:$A$292=Sheet1!$C$5)*(Sheet1!$B15=Sheet2!$D$1:$D$292),0))),"",INDEX(Sheet2!$G$1:$G$292,MATCH(1,(Sheet2!$A$1:$A$292=Sheet1!$C$5)*(Sheet1!$B15=Sheet2!$D$1:$D$292),0))) CSE

Brilliant!!! Thanks very much for that!

Because there will be added lines in the future is there a way to change the ranges on 'Sheet 2' to the whole column rather than the selected range in the formula?

Originally Posted by eoghanmolloy

Brilliant!!! Thanks very much for that!

Because there will be added lines in the future is there a way to change the ranges on 'Sheet 2' to the whole column rather than the selected range in the formula?

Yes you can enlarge the function. Just change the number "292" to the whatever number you want (ex 1000). But ranges have to be equal. For example if you select 1:1000 range it have to be at the same range in other columns too

Thats what I thought, thanks again.

I am trying to run a macro for using this formula but excel is prompting me with a message stating 'unable to record'. when I do it on our 'Test' spreadsheet the macro works fine but trying to record one on my actual spreadsheet doesnt work. This is the formula I am using:

=IF(ISERROR(INDEX('[Master Spreadsheet v1.6.xls]Master Data'!$G$10:$G$301,MATCH(1,('[Master Spreadsheet v1.6.xls]Master Data'!$A$10:$A$301=$C$5)*($B15='[Master Spreadsheet v1.6.xls]Master Data'!$D$10:$D$301),0))),"",INDEX('[Master Spreadsheet v1.6.xls]Master Data'!$G$10:$G$301,MATCH(1,('[Master Spreadsheet v1.6.xls]Master Data'!$A$10:$A$301=$C$5)*($B15='[Master Spreadsheet v1.6.xls]Master Data'!$D$10:$D$301),0)))

Do you know the code I can use on VB to get this to work?

Thanks

I am sorry But my VBA level is something like beginner. For this problem you should ask your question to Excel Gurus