|
index_match_error!
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
index_match_error!
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" <u23552@uwe wrote in message news:688b7f7dce1e8@uwe... 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
index_match_error!
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 Phillips wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
index_match_error!
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) "via135 via OfficeKB.com" <u23552@uwe wrote in message news:688beca90e9e4@uwe... 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 Phillips wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
index_match_error!
hi!
still i am getting the same error #NA -via135 Bob Phillips wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
index_match_error!
via135 wrote:
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
index_match_error!
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) "via135 via OfficeKB.com" <u23552@uwe wrote in message news:688c515ccb882@uwe... via135 wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
index_match_error!
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 Bob Phillips wrote: Another alternative =INDEX(Sheet1!B1:B4,MATCH(1,--(LEFT(Sheet1!A1:A4,3)=TEXT(A1,"General")),0)) hi! [quoted text clipped - 14 lines] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
index_match_error!
sorry..Bob..!
it works correctly. mistake is on my part giving the index array wrong..!! thks for the help! via135 via135 wrote: 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] -via135 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200610/1 |
| All times are GMT +1. The time now is 04:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com