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" wrote:

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$20 500='Model!$C$8)*(Data!$B$2:$B$20500=Model!A12)*(D ata!$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.
--
when u change the way u look @ things, the things u look at change.