Dec 04 2020 05:33 AM
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 |
Dec 04 2020 05:42 AM
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?
Dec 04 2020 08:37 AM
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.