Index Match function for lookup HELP
 

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,Citr us!$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?

Index Match function for lookup HELP
 

"Skye1973" wrote:
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,Citr us!$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.

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 the iserror portion. This is the first time I have gotten this index/match to work correctly.

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

Your original formula was looking at differing range sizes. So in the one instance you're looking at a range down to row 539 and then only down to 296. It's far easier to keep an eye on where these things could be going wrong if your range sizes match.

Also in the first MATCH you are looking at multiple columns where you should be looking at only one column.

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

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

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 and ISERROR then doubling up the formula. So the formula in F5 and copied down would be:

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

Hope that helps. :)

I will be checking that out!

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

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

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

Not a problem. Always happy to help. :)

Index Match function for lookup HELP
 

"Skye1973" wrote:
Spencer101;1605315 Wrote:
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.