If function between tables

[Emmanuelle_Paquay]

Hi everybody, I use the if function between 2 tables:

=IF([@[ATO Reference]]=ATO_Database[@[ATO Reference]];ATO_Database[@[ATO Official name]]), so I can automatically fill in the name of the ATO in my working table.

It works well for the first 5-6 rows then gives a FALSE answer. I notice that, in the database table, the next item is BE/ATO-014.  If I add this item in the working table (I use the formula in), I'll get the right answer unless there is once again a gap between the ATO reference in the 2 tables (I hope it's clear).

Any idea on how to solve that?

Thanks a lot

ATO Reference                           ATO_Name

BE/ATO-001	AAAA
BE/ATO-005	BBBB
BE/ATO-006	CCCC
BE/ATO-010	DDDD
BE/ATO-013	EEEE
BE/ATO-017	FALSE
BE/ATO-023	FALSE
BE/ATO-101	FALSE

[#2]

@Emmanuelle_Paquay 

Some ideas, yes, but without the actual spreadsheet, no way to test or verify. Is it possible to post the spreadsheet? Just no proprietary or confidential info, please.

That said, are there relative references in the formula that need to be absolute? Or vice versa?

[#3]

@Emmanuelle_Paquay 

What you do is like

---

for the row $5

if first table [ATO Reference] is equal to [ATO Reference] in second table

then something

else FALSE

---

Other words you compare values only for the same row number. If match in any row when it shall be INDEX/MATCH, XLOOKUP or like.