Any methods in order to avoid #NA by using Formula and deriving its value using the respective Formula

[RapchikM]

Hello Friends

Wishing All Happy New Year 2024

I've Sheet named as Numbers as Main Data Reference Sheet

In Sheet 2 Cell A26 Contains the Following Formula

=TEXTJOIN(" ",,IF($H$13:$Q$13=$G$22,$H$13:$Q$13,""))

So value Displayed as "1 1 1 1" in Sheet2 To indicate the spacing value displayed between ""


In Sheet 2 Cell C26 Contains the Following Formula
=INDEX('Numbers'!$B$3:$B$22,MATCH(A26,'Numbers'!$A$3:$A$22,0))


So value Displayed as "Company" in sheet named Numbers in Cell C26 so derived correctly from Sheet Named Numbers

When i drag the formula from A26 to A27 and to further Bottom I get Error

So now In Sheet 2 Cell A27 Contains the Following Formula
=TEXTJOIN(" ",,IF($H$13:$Q$13=$H$20,$H$13:$Q$13,""))

So value Displayed as "2" in Sheet2 in cell A27

In Sheet 2 Cell C27 Contains the Following Formula
=INDEX('Numbers'!$B$3:$B$7,MATCH(A27,'Numbers'!$A$3:$A$22,0))


I get Error #NA in Cell C27

But if i Type only 2 in A27 formula works perfectly without #NA error
I don't know what's going on

Any methods in order to avoid #NA by using Formula and deriving its value using the respective Formula

BTW there are no Spaces after Text in Column B of Sheet Numbers

Your Help will be appreciated

Thanks
RapchikM

 

[DanteAmor]

So value Displayed as "2" in Sheet2 in cell A27

But if i Type only 2 in A27 formula works perfectly without #NA error
I don't know what's going on

The result of the formula is text, but on your sheet you have a numerical value.


Try:

=IFERROR(INDEX(Numbers!$B$3:$B$7,MATCH(A27,Numbers!$A$3:$A$22,0)),INDEX(Numbers!$B$3:$B$7,MATCH(VALUE(A27),Numbers!$A$3:$A$22,0)))

 

[RapchikM]

Thank you DanteAmor Sir,

=IFERROR(INDEX(Numbers!$B$3:$B$7,MATCH(A27,Numbers!$A$3:$A$22,0)),INDEX(Numbers!$B$3:$B$7,MATCH(VALUE(A27),Numbers!$A$3:$A$22,0)))

Worked perfectly

Last but not the Least
am getting #Value if the Cells in Col A is Blank or with the below Formula as per my #1 post of this thread used for Cell A27
=TEXTJOIN(" ",,IF($H$13:$Q$13=$H$20,$H$13:$Q$13,"")) for Eg. What if Value in the range from H to Q is not found and therefore it remains blank
and probably for this reason i am getting error #Value

Will indeed appreciate your help in this lengthy formula instead of #Value better representation would be "Missing"

Thanks
RapchikM

 

[DanteAmor]

If A27 si blank

Try this:

=IF(A27="","",IFERROR(INDEX(Numbers!$B$3:$B$7,MATCH(A27,Numbers!$A$3:$A$22,0)),INDEX(Numbers!$B$3:$B$7,MATCH(VALUE(A27),Numbers!$A$3:$A$22,0))))

 

[RapchikM]

Thank you Sir for your indeed valuable input

Try this:
Excel Formula:
=IF(A27="","",IFERROR(INDEX(Numbers!$B$3:$B$7,MATCH(A27,Numbers!$A$3:$A$22,0)),INDEX(Numbers!$B$3:$B$7,MATCH(VALUE(A27),Numbers!$A$3:$A$22,0))))

Only thing i changed

=IF(A27="","MISSING",IFERROR(INDEX(Numbers!$B$3:$B$7,MATCH(A27,Numbers!$A$3:$A$22,0)),INDEX(Numbers!$B$3:$B$7,MATCH(VALUE(A27),Numbers!$A$3:$A$22,0))))

RapchikM