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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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