Index Match function for lookup HELP

[Skye1973]

I have two worksheets where I am wanting to match data on tab 1 to dat
on tab two and return the intersecting value. I am using the formul
below and keep returning the value of 0?

=IF(ISERROR(INDEX(Citrus!$A$1:$I$539,MATCH(A5,Citrus!$A$1:$H$296,0),MATCH($D$1,Citrus!$A$1:$Z$1,0))),0,INDEX(Citrus!$A$1:$I$539,MATCH(A5,Citrus!$A$1:$A$296,0),MATCH($D$1,Citrus!$A$1:$Z$1,0)))

What am I doing wrong

+-------------------------------------------------------------------
|Filename: example.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=572
+-------------------------------------------------------------------

 

[joeu2004]

I have two worksheets where I am wanting to match data
on tab 1 to data on tab two and return the intersecting
value. I am using the formula below and keep returning
the value of 0?

=IF(ISERROR(INDEX(Citrus!$A$1:$I$539,MATCH(A5,Citrus!$A$1:$H$296,0),
MATCH($D$1,Citrus!$A$1:$Z$1,0))),0,INDEX(Citrus!$A$1:$I$539,
MATCH(A5,Citrus!$A$1:$A$296,0),MATCH($D$1,Citrus!$A$1:$Z$1,0)))

What am I doing wrong?

For one thing, the range Citrus!$A$1:$H$296 in the ISERROR should be
Citrus!$A$1:$A$296, as it is in the value-if-true expression.

 

[Spencer101]

I have two worksheets where I am wanting to match data on tab 1 to dat
on tab two and return the intersecting value. I am using the formul
below and keep returning the value of 0?

=IF(ISERROR(INDEX(Citrus!$A$1:$I$539,MATCH(A5,Citrus!$A$1:$H$296,0),MATCH($D$1,Citrus!$A$1:$Z$1,0))),0,INDEX(Citrus!$A$1:$I$539,MATCH(A5,Citrus!$A$1:$A$296,0),MATCH($D$1,Citrus!$A$1:$Z$1,0)))

What am I doing wrong?

Hi,

Try the below formula in cell F5 and copy down:

=INDEX(Citrus!$A$1:$G$56,MATCH($A5,Citrus!$A$1:$A$56,0),MATCH(D$1,Citrus!$A$1:$G$1,0))

Does that do what you need

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Skye1973]

Hi,

Try the below formula in cell F5 and copy down:

=INDEX(Citrus!$A$1:$G$56,MATCH($A5,Citrus!$A$1:$A$56,0),MATCH(D$1,Citrus!$A$1:$G$1,0))

Does that do what you need?

THANK YOU!!! This seems to do exactly what I need I just need to add th
iserror portion. This is the first time I have gotten this index/matc
to work correctly.

Any thoughts based on my original formula where my logic is off

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Skye1973]

Have a read of http://www.contextures.com/xlFunctions03.html . It give
lots of clear information on how to use INDEX & MATCH.

I will be checking that out!

Having had a look again at your formula I an confused as to why you'r
using the IF(ISERROR approach. Are you just using this to produce
zero if no match is found?

Yes the in the real workbook I will return several no match's that nee
to reflect a 0 vs an error.

I presume from the number of rows in your example workbook (1million+
that you're using Excel 2007 or later?
If this is the case then you can use =IFERROR() rather than an IF an
ISERROR then doubling up the formula. So the formula in F5 and copie
down would be:

Yes we just upgraded from 2003 to 2010 and I am still getting used t
the expanded functionality and new functions. Thanks for the tip o
IFERROR the formula works perfectly!

Your post has been EXTREMLEY helpful in furthering my understanding o
this combination of functions. Thanks so much

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[joeu2004]

Try the below formula in cell F5 and copy down:
=INDEX(Citrus!$A$1:$G$56,MATCH($A5,Citrus!$A$1:$A$56,0),
MATCH(D$1,Citrus!$A$1:$G$1,0))

[....]
Any thoughts based on my original formula where my logic is off?

I believe I answered that already. As I noted, you have a typo in the
ISERROR expression.

When I fix that, your original formula seems to work the same as Spencer's
rewrite without the loss of functionality (error detection).

Granted, you do not seem to need the error detection, at least with the
example Excel file. And granted, Spencer's rewrite simplifies your original
formula in ways that are consistent with the example Excel file.

But we cannot know if the example Excel file is representative of your total
and future needs. Only you can make that determination.