Vlookup - how to deal with #n/a errors - office 365

Question

I'm using MS Excel O365 Business in version 1905 (compilation 11629.20196). In this case i have created two sheets: 1) "Orders"

2) Order_status

NUMBER_ORDER Column (in Order_status sheet) has been created all cells by drop down list giving source to column in "Orders" sheet: =Orders!$B$2:$B$1048576

Now i'd like to create a formula in DESC_ORDER from "Order_status" sheet which automatically writes data from cell in "NUMBER_ORDER" Column. By writing that formula i have "#N/A" Error (despite the correction all data in the 2 sheets).

What I've tried?

I tried in a few different ways:

1) I've written =VLOOKUP(E2,Orders!$A:$B,COLUMN(Orders!$A$2:$A$1048576)) then i formated text to column the DESC_ORDER in "Orders" sheet. It didn't work.

2) Then i tried this VLOOKUP by changing to text in E2 cell: =VLOOKUP(TEXT(E2,"#"),Orders!$A:$B,COLUMN(Orders!$A$2:$A$1048576)). Didn't work too.

3) Then i modified to Trim in E2 cell: =VLOOKUP(TRIM(E2),Orders!$A:$B,COLUMN(Orders!$A$2:$A$1048576)) Didn't help.

4) Later i created 2 views "Orders" for these 2 columns for in "Orders" sheet. And "Orders_status" in columns from "Order_status" sheet. The i rewrote that formula: =VLOOKUP(Order_status,Orders!$A:$B,1,FALSE) and still shows #N/A! Error.

I've been looking for any solution but i haven't still found. Can someone please what should i do? Any ideas? Thx in advance for help or any tips.

Answer 1

Try:

=INDEX(Orders!$A$2:$A$6,MATCH(E2,Orders!$B$2:$B$6,0))

Results: