Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
Bob said: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.
[quoted text clipped - 8 lines]-via135
via135 via OfficeKB.com said: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
Bob said: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.
[quoted text clipped - 8 lines]-via135
Bob said: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.
[quoted text clipped - 12 lines]
via135 said:hi!
still i am getting the same error #NA
-via135
[quoted text clipped - 8 lines]I see. Try this then
via135 via OfficeKB.com said:via135 said:hi!
still i am getting the same error #NA
-via135
[quoted text clipped - 8 lines]I see. Try this then-via135
yes..
it works when the look-up value is text
and gives #NA error when the same is a number string!
-via135
Bob said:Another alternative
=INDEX(Sheet1!B1:B4,MATCH(1,--(LEFT(Sheet1!A1:A4,3)=TEXT(A1,"General")),0))
[quoted text clipped - 14 lines]-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
[quoted text clipped - 5 lines]Another alternative
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.