Index and Match Functions
 

Good Morning All,

I have read some peoples suggestions as to how to lookup a value based on
two or more criteria using a combination of INDEX and MATCH fuctions. I have
tried to repicate this myself however my fuctions always return a #NA error.
Below is the data and function as it appears normally and under that is the
same range, showing the formula. I am try to match a model and a serial and
return the result (WO or R). I can't see why this formula isn't working, as
far as I've seen its should work on a combination of letters and numbers. If
anyone can see how to correct this could you please let me know.

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509
=INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
AOT24RZAL T005294 R AOT24RZAL T006183
=INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
AOT24RZAL T007509 WO AOT24RZAL
T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))

Index and Match Functions
 

you need to array enter it, it should be committed with Ctrl-Shift-Enter,
not just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Damien" wrote in message
...
Good Morning All,

I have read some peoples suggestions as to how to lookup a value based on
two or more criteria using a combination of INDEX and MATCH fuctions. I
have
tried to repicate this myself however my fuctions always return a #NA
error.
Below is the data and function as it appears normally and under that is
the
same range, showing the formula. I am try to match a model and a serial
and
return the result (WO or R). I can't see why this formula isn't working,
as
far as I've seen its should work on a combination of letters and numbers.
If
anyone can see how to correct this could you please let me know.

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509
=INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
AOT24RZAL T005294 R AOT24RZAL T006183
=INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
AOT24RZAL T007509 WO AOT24RZAL
T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))

Index and Match Functions
 

Sorry I forgort to mention, I've already done that, it still gives the same
result. Any other ideas?

"Bob Phillips" wrote:

you need to array enter it, it should be committed with Ctrl-Shift-Enter,
not just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Damien" wrote in message
...
Good Morning All,

I have read some peoples suggestions as to how to lookup a value based on
two or more criteria using a combination of INDEX and MATCH fuctions. I
have
tried to repicate this myself however my fuctions always return a #NA
error.
Below is the data and function as it appears normally and under that is
the
same range, showing the formula. I am try to match a model and a serial
and
return the result (WO or R). I can't see why this formula isn't working,
as
far as I've seen its should work on a combination of letters and numbers.
If
anyone can see how to correct this could you please let me know.

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509
=INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
AOT24RZAL T005294 R AOT24RZAL T006183
=INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
AOT24RZAL T007509 WO AOT24RZAL
T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))

Index and Match Functions
 

No ideas I am afraid. The absolute version

=INDEX($C$29:$C$31,MATCH(D29&E29,$A$29:$A$31&$B$29 :$B$31,0))

works fine for me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Damien" wrote in message
...
Sorry I forgort to mention, I've already done that, it still gives the
same
result. Any other ideas?

"Bob Phillips" wrote:

you need to array enter it, it should be committed with
Ctrl-Shift-Enter,
not just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Damien" wrote in message
...
Good Morning All,

I have read some peoples suggestions as to how to lookup a value based
on
two or more criteria using a combination of INDEX and MATCH fuctions.
I
have
tried to repicate this myself however my fuctions always return a #NA
error.
Below is the data and function as it appears normally and under that
is
the
same range, showing the formula. I am try to match a model and a
serial
and
return the result (WO or R). I can't see why this formula isn't
working,
as
far as I've seen its should work on a combination of letters and
numbers.
If
anyone can see how to correct this could you please let me know.

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A

Unit Serial Result Find Unit Find Serial Produce
Result
AOT24RZAL T006183 WO AOT24RZAL T007509
=INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
AOT24RZAL T005294 R AOT24RZAL T006183
=INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
AOT24RZAL T007509 WO AOT24RZAL
T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))

Index and Match Functions
 

99% of the time, the reason that suggested solutions (formulas) don't work
for the OPs, and *do* work for the responders, is that the responders test
their suggestions with their own keyed in data which is an *exact* match
between the datalist and the lookup list, whereas the OPs data is a return
of a formula or an import from another data source.

This returned or imported data most often turns out to *not* be what the OP
expected, and therefore did not accurately depict in their (OP) lookup list
(formula).

There's almost always imbedded, invisible characters (spaces - web
characters) that contaminate the datalist, making exact matches
unachievable.

One true test of the veracity of the formula *itself*, is to manually key in
test data in the datalist and the lookup list to insure that there is an
exact match between them both.

If that makes the formula perform as expected, then the data must be
examined for contamination.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Damien" wrote in message
...
Sorry I forgort to mention, I've already done that, it still gives the same
result. Any other ideas?

"Bob Phillips" wrote:

you need to array enter it, it should be committed with Ctrl-Shift-Enter,
not just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Damien" wrote in message
...
Good Morning All,

I have read some peoples suggestions as to how to lookup a value based
on
two or more criteria using a combination of INDEX and MATCH fuctions. I
have
tried to repicate this myself however my fuctions always return a #NA
error.
Below is the data and function as it appears normally and under that is
the
same range, showing the formula. I am try to match a model and a serial
and
return the result (WO or R). I can't see why this formula isn't
working,
as
far as I've seen its should work on a combination of letters and
numbers.
If
anyone can see how to correct this could you please let me know.

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509 #N/A
AOT24RZAL T005294 R AOT24RZAL T006183 #N/A
AOT24RZAL T007509 WO AOT24RZAL T005294 #N/A

Unit Serial Result Find Unit Find Serial Produce Result
AOT24RZAL T006183 WO AOT24RZAL T007509
=INDEX(C29:C31,MATCH(D29&E29,A29:A31&B29:B31,0))
AOT24RZAL T005294 R AOT24RZAL T006183
=INDEX(C29:C31,MATCH(D30&E30,A29:A31&B29:B31,0))
AOT24RZAL T007509 WO AOT24RZAL
T005294 =INDEX(C29:C31,MATCH(D31&E31,A29:A31&B29:B31,0))