#N/A error in excel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Just forgot to add that, #N/A error means no matching row was returned by the Match formula.

Try

=INDEX(Sheet1!V:V,iferror(MATCH(E6,Sheet1!G:G,0),MATCH(E6&"",Sheet1!G:G,0)))

ASKER

Thanks for the info.

ASKER

So I just found a match but I can't get the formula to see it. The cell shows to be formatted as GENERAL on both sheets?????

However it is a number on both sheets.

A cell can store a number as text even you see it's format as General. To check that, use ISNUMBER formula to check whether the cell contains an actual number or a number stored as text.

In any case, you can use one of the formulas I suggested.

ASKER

How do I use the ISNUMBER formula to check it??

Shums Faruk

Please upload a sample workbook :)

e.g. to check whether the cell E6 has a real number in it, place the following formula in any empty cell and if it returns TRUE, that means E6 contain a real number and if it returns FALSE, that means E6 contains a number stored as text.

=ISNUMBER(E6)

Open in new window

ASKER

Check this out.
TEST.xlsx

Did you try this?

=IFERROR(INDEX(Sheet1!A:A,MATCH(B2,Sheet1!B:B,0)),"")

Open in new window

Shums Faruk

Try below Array Formula confirmed with Ctrl+Shift+Enter:

=IFERROR(INDEX(Sheet1!A:A,MATCH(TRUE,ISNUMBER(SEARCH($B2,Sheet1!B:B)),0)),"Not Found")

Open in new window

It will return both numeric & text values
check in attached...
ssblue_test_Index_Match_v1.xlsx

I see that you are using my formula in your spreadsheet.

BTW the searched numbers are not present in sheet1 that is why #NA

Maybe I am misunderstanding your problem. Which row is giving the problem?

ASKER

If you look at rows 48 and 49 you will see where the issue is. There is a match for these numbers but it is returning #N/A

ASKER

...not to mention that I have several thousand rows and this is an issue on more than one case.

You have not copied my formula all the way down. Copy it and then you will get the answers.

ASKER

Thanks Shums!!!!

ASKER

One last question - why can't I copy and paste that formula and it work???