If function between tables

%3CLINGO-SUB%20id%3D%22lingo-sub-1956082%22%20slang%3D%22en-US%22%3EIf%20function%20between%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956082%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everybody%2C%20I%20use%20the%20if%20function%20between%202%20tables%3A%3C%2FP%3E%3CP%3E%3DIF(%5B%40%5BATO%20Reference%5D%5D%3DATO_Database%5B%40%5BATO%20Reference%5D%5D%3BATO_Database%5B%40%5BATO%20Official%20name%5D%5D)%2C%20so%20I%20can%20automatically%20fill%20in%20the%20name%20of%20the%20ATO%20in%20my%20working%20table.%3C%2FP%3E%3CP%3EIt%20works%20well%20for%20the%20first%205-6%20rows%20then%20gives%20a%20FALSE%20answer.%20I%20notice%20that%2C%20in%20the%20database%20table%2C%20the%20next%20item%20is%20BE%2FATO-014.%26nbsp%3B%20If%20I%20add%20this%20item%20in%20the%20working%20table%20(I%20use%20the%20formula%20in)%2C%20I'll%20get%20the%20right%20answer%20unless%20there%20is%20once%20again%20a%20gap%20between%20the%20ATO%20reference%20in%20the%202%20tables%20(I%20hope%20it's%20clear).%3C%2FP%3E%3CP%3EAny%20idea%20on%20how%20to%20solve%20that%3F%3C%2FP%3E%3CP%3EThanks%20a%20lot%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EATO%20Reference%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BATO_Name%3C%2FP%3E%3CTABLE%20width%3D%22347%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EBE%2FATO-001%3C%2FTD%3E%3CTD%3EAAAA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBE%2FATO-005%3C%2FTD%3E%3CTD%3EBBBB%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBE%2FATO-006%3C%2FTD%3E%3CTD%3ECCCC%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBE%2FATO-010%3C%2FTD%3E%3CTD%3EDDDD%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBE%2FATO-013%3C%2FTD%3E%3CTD%3EEEEE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBE%2FATO-017%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBE%2FATO-023%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBE%2FATO-101%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1956082%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1956100%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20between%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956100%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891081%22%20target%3D%22_blank%22%3E%40Emmanuelle_Paquay%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20ideas%2C%20yes%2C%20but%20without%20the%20actual%20spreadsheet%2C%20no%20way%20to%20test%20or%20verify.%20Is%20it%20possible%20to%20post%20the%20spreadsheet%3F%20Just%20no%20proprietary%20or%20confidential%20info%2C%20please.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20said%2C%20are%20there%20relative%20references%20in%20the%20formula%20that%20need%20to%20be%20absolute%3F%20Or%20vice%20versa%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1956626%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%20between%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956626%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891081%22%20target%3D%22_blank%22%3E%40Emmanuelle_Paquay%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20you%20do%20is%20like%3C%2FP%3E%0A%3CP%3E---%3C%2FP%3E%0A%3CP%3Efor%20the%20row%20%245%3C%2FP%3E%0A%3CP%3Eif%20first%20table%26nbsp%3B%3CSPAN%3E%5BATO%20Reference%5D%20is%20equal%20to%26nbsp%3B%5BATO%20Reference%5D%20in%20second%20table%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3Ethen%20something%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3Eelse%20FALSE%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E---%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EOther%20words%20you%20compare%26nbsp%3Bvalues%20only%20for%20the%20same%20row%20number.%20If%20match%20in%20any%20row%20when%20it%20shall%20be%20INDEX%2FMATCH%2C%20XLOOKUP%20or%20like.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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-001AAAA
BE/ATO-005BBBB
BE/ATO-006CCCC
BE/ATO-010DDDD
BE/ATO-013EEEE
BE/ATO-017FALSE
BE/ATO-023FALSE
BE/ATO-101FALSE
2 Replies

@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?

 

@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.