index_match_error!

[via135 via OfficeKB.com]

hi!

i am getting #NA error for the following
array entered formula!

=INDEX(Sheet1!B1:B4,MATCH(A1,LEFT(Sheet1!A1:A4,3),0))

what i am doing wrong?

help pl!

-via135

 

[Bob Phillips]

Why are you trying to match A1 against the first 3 chars of A1 (and 2 and 3
and 4)? It can only match if A1 is 3 chars.

--
HTH

Bob Phillips

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

 

[via135 via OfficeKB.com]

hi!

actually my look-up value A1 (3 characters) is in sheet2
where as the look-up array A1:A4 (4 characters) is in sheet1...
and the formula is in B1 of sheet2..!

-via135

Why are you trying to match A1 against the first 3 chars of A1 (and 2 and 3
and 4)? It can only match if A1 is 3 chars.

hi!

[quoted text clipped - 8 lines]

-via135

 

[Bob Phillips]

I see. Try this then

=INDEX(Sheet1!B1:B4,MATCH(1,--(LEFT(Sheet1!A1:A4,3)=A1),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

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

hi!

actually my look-up value A1 (3 characters) is in sheet2
where as the look-up array A1:A4 (4 characters) is in sheet1...
and the formula is in B1 of sheet2..!

-via135

Why are you trying to match A1 against the first 3 chars of A1 (and 2 and 3
and 4)? It can only match if A1 is 3 chars.

hi!

[quoted text clipped - 8 lines]

-via135

 

[via135 via OfficeKB.com]

hi!

still i am getting the same error #NA

-via135

I see. Try this then

=INDEX(Sheet1!B1:B4,MATCH(1,--(LEFT(Sheet1!A1:A4,3)=A1),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

hi!

[quoted text clipped - 12 lines]

 

[via135 via OfficeKB.com]

hi!

still i am getting the same error #NA

-via135

I see. Try this then

[quoted text clipped - 8 lines]

yes..

it works when the look-up value is text
and gives #NA error when the same is a number string!

-via135

 

[Bob Phillips]

Another alternative

=INDEX(Sheet1!B1:B4,MATCH(1,--(LEFT(Sheet1!A1:A4,3)=TEXT(A1,"General")),0))

--
HTH

Bob Phillips

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

hi!

still i am getting the same error #NA

-via135

I see. Try this then

[quoted text clipped - 8 lines]

-via135

yes..

it works when the look-up value is text
and gives #NA error when the same is a number string!

-via135

 

[via135 via OfficeKB.com]

hi!

this one works good when the return value (sheet1!b1:b4)
is as long as text. when there is a number in the index
array, again i am getting the
same error #NA..???!!!

-via135

Another alternative

=INDEX(Sheet1!B1:B4,MATCH(1,--(LEFT(Sheet1!A1:A4,3)=TEXT(A1,"General")),0))
[quoted text clipped - 14 lines]

-via135

 

[via135 via OfficeKB.com]

sorry..Bob..!

it works correctly. mistake is on my part
giving the index array wrong..!!

thks for the help!

via135

hi!

this one works good when the return value (sheet1!b1:b4)
is as long as text. when there is a number in the index
array, again i am getting the
same error #NA..???!!!

-via135

Another alternative

[quoted text clipped - 5 lines]