Number vs text format using MATCH function

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
126
Office Version
2016
Platform
Windows
https://1drv.ms/x/s!AvjBsEPEq12ngTMpaLVtqgZcYXLI?e=Hm2JrH

I am trying to do a Match lookup.
The value I am looking up could be in text or number format.
The search value can also be in text or number format.

What formula could I use that would work for all different combinations of formats, that is:

Number looking up number
number looking up text
text looking up number
text looking up text

E.g. in the link, I am looking up an invoice number. It is clearly there but the MATCH formula gets an N/A error.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,538
Office Version
365
Platform
Windows
Maybe something like this.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCD
1LookupMatch
212345112345
3223A45
4A45222
Sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,102,370
Messages
5,486,460
Members
407,548
Latest member
FayP

This Week's Hot Topics

Top