VLOOKUP, INDEX & MATCH ERROR HELP

[sahafi]

Hi All,

I keep getting #N/A error every time I execute this formula. Here's the
situation:
My lookup range data resides in 'Data' sheet, and my formulas in 'Model'
sheet.
Data sheet:
YEAR, MONTH, WK, PROD_NUM, COL_E, POUNDS, COL_G, COL_H, COL_I, REGION.
I need to lookup 5 values from the 'Model' sheet and return values from:
POUNDS Column in the 'Data' sheet, if all other 4 values matches: YEAR,
MONTH, WK, and PROD_NUM.
I have tried both the VLOOKUP, and the INDEX/MATCH formulas with no success.
Here's the two formulas:
** =IF(ISERROR(VLOOKUP(($C$8 & A12 & B12 & $A$7 & $D$9),
Data!$A:$K,6,FALSE)),"",(VLOOKUP(($C$8 & A12 & B12 & $A$7 & $D$9),
Data!$A:$K,6,FALSE)))

**
=INDEX(Data!$F$2:$F$20500,MATCH(1,(Data!$A$2:$A$20500='Model!$C$8)*(Data!$B$2:$B$20500=Model!A12)*(Data!$C$2:$C$20500='Model!B12)*(Data!$D$2:$D$20500='Model!$A$7)*(Data!$J$2:$J$20500='Model!$E$9),0))

I have committed to the 2nd formula with Shift+Ctrl+Enter. It does create
the { } around the formula, but i'm getting only #N/A.
The first 4 fields in my Data sheet are formatted as text and matches the
corresponding values on the Model sheet, while the pounds field is set up as
number.
** the data on the 'Data' sheet was imported from Access query up to column
H while the last 2 columns (I & J) are generated by a vlookup formulas.

Any help on this is really appreciated.

Thanks.

 

[Toppers]

The second formula works OK for me with Ctrl+Shift+Enter. You mention column
I in your posting but this is not used in the formula.

Also in formula 1 the search value is D9 but in the second formula (index)
it is E9 .. is this the cause of your error (or a typo)?

 

[sahafi]

Thanks for the reply. Yes that's was a typo, it should be 'E9' in both.
Basically i'm looking up 5 values from the 'Model' sheet to match values in
Columns (A, B, C, D, and J) on the 'Data' sheet, then return the
corresponding numerical value from Column 'F'. For this particular task, i'm
not using columns G, H, or I on the formula. I have mentioned that just in
case someone would like to know the sequence of my lookup range (A - J) but
matching: A,B,C,D,J.
How did you get the array formula to work? Because I've tried it many times,
but I keep getting the #N/A error. Am I missing something here? I have a
feeling it has to do with how Access format the data, and how Excel interpret
that, but I did check the values carefully.. I even used the '--' and the
'Text' function to make sure that Excel will read the data format correctly.

Thanks.

 

[Toppers]

If you want, send a copy of w/sheet to:

toppers<at>johntopley.fsnet.co.uk

 

[sahafi]

I have sent you a copy of the file about a couple of hours ago. Please let me
know, if you haven't received it.

Thanks for your help.

 

[sahafi]

I think I've got the INDEX/MATCH to work, but how can I incorporate the
'SUMIF' function within the same formula so as to sum the pounds by region
(EAST, WEST) for the specified YEAR, PD, WK, and PROD_NUM? Or is there any
other function besides SUMIF that would do the job.

Thanks.

 

[sahafi]

Toppers,

I have replaced the INDEX/MATCH function with the 'SUMPRODUCT' and it worked
excellent.
I'm still trying to figure out how to set up a macro to automate the data
import from Access to Excel (have tried TransferSpreadSheet in Access, but
didn't work) not sure to set it up on Access, or Excel.

Thanks a bunch.