if statement with numbers stored as text

[jb00976]

I have a spreadsheet called "Toronto" where column D contains numbers stored as text customer numbers like 01234567.

I have a second spreadsheet called "HO Report" where column C ("Customer Number") has another list of customer numbers (same format also stored as text) and has column L ("ACQ/REP") which has the text values of either "Acq" or "Rep". This spreadsheet is formatted as a table

I want to use an if statement to see if Column C in the "Toronto" spreadsheet matches column D in the "HO Report" spreadsheet and if so return the corresponding information in column L in the "HO Report" spreadsheet.

I have tried the following

=if(Table1[Customer Number],'Toronto'!D159,Table1[ACQ/REP],"NO")

but, all I get back is "No" every time.

Please help!

Thank you in advance.

 

[hernantorres23]

Hi. If you want find only a match, you can use function Match(ValueToFind,RangeToFind,0)
If you need return values associated to this match, you could use VLOOKUP, Index + Match, XLOOKUP (require OFFICE 365)

If you want more help, please try prepare a data example using add-in specifically for this, it can be found here XL2BB
Pay attention to this post XL2BB 2 Square