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

RapchikM

Board Regular
Joined
Oct 5, 2020
Messages
97
Office Version
  1. 2021
Platform
  1. Windows
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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:

Excel Formula:
=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)))
 
Upvote 0
Thank you DanteAmor Sir,
Excel Formula:
=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
 
Upvote 0
If A27 si blank

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))))

😇
 
Upvote 0
Solution
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
Excel Formula:
=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
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top