| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
