Array / Concatenate formula issue

**Ozwilly** · 10-25-2011, 11:06 PM

Hi,

Two issues would love to get advice on. Firstly, if I try to write an array formula (using Excel 2007), when I press Cntrl + Shift + Enter, nothing happens - no brackets. Why?

The second one is a little more complex. I have a file that I want to use an index match for, using multiple criteria. For some reason it will not work. What I am wanting to do is to look up a table, and if the date matches what is in cell G3, and the word matches what is in B4, & the URL matches what is in C4, the value is returned, otherwise shows "". I have not even gotten as far as putting in the "" to leave the cell blank if no data as cannot make the formula work

This is the formula I am using, which according to the Microsoft site is a concatenate formula which will might be better for satisfying multiple criteria in an index match.

=INDEX('Market Samurai data'!$E:$E,MATCH(B4&C4&G$3,'Market Samurai data'!$A:$A&'Market Samurai data'!$B:$B&'Market Samurai data'!$C:$C,0))

Have attached the relevant file, and appreciate any help

**ConneXionLost** · 10-25-2011, 11:42 PM

You need to arrange the lookup values in the same order as the array. Try this:

Please Login or Register  to view this content.

confirmed with CSE.

Cheers,

**Ozwilly** · 10-26-2011, 12:00 AM

Thanks, tried it however am still getting VALUE error. I have tried doing text to column, retyping data to ensure the lookup is correct etc?????

Also tried doing CSE & it does not put {} around the formula???

**abousetta** · 10-26-2011, 01:01 AM

I put the formula that ConneXionLost provided and it worked using Ctrl + Shift + Enter instead of Enter only. Give another go. The reason you are getting #Value is because it was entered as a regular formula (Enter only).

Hope this helps.

abousetta

**ConneXionLost** · 10-26-2011, 02:06 AM

Here's what I have.

**ConneXionLost** · 10-26-2011, 02:21 AM

How to use Control-Shift-Enter:

- select the cell with the array formula in it

- on the formula bar, click (place your cursor) at the end of the formula

- press Control-Shift-Enter

**Ozwilly** · 10-27-2011, 04:41 AM

Hi,

Thanks everyone for your assistance

I have been able to get the array formula to work finally which is great. I notice it is quite slow to calculate (I have an I7 laptop so should not be a problem). Is this common with array formulas? I read somewhere that a nested IF formula will do the same thing however concatenate is better and more stable? Given this file will have a lot more cells to calculate than just what I have in this example I wanted to make sure it is going to be stable enough to use?

I am also trying to put in an ISNA to make the cell blank ("") if there is no value, however cannot get this to work as am being told I have too many arguments in the formula, so not sure how to do this? I have attached the updated file. Appreciate any help