View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Toppers Toppers is offline
external usenet poster
 
Posts: 4,339
Default VLOOKUP, INDEX & MATCH ERROR HELP

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

toppers<atjohntopley.fsnet.co.uk


"sahafi" wrote:

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.
--
when u change the way u look @ things, the things u look at change.


"Toppers" wrote:

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.