If combined with Vlookup in MS Access

[excel_1317]

I am new to Access, just wanted to ensure that is below is achievable in MS Access.

=IF(IFNA(VLOOKUP(C4,$A:$A,1,0),"")=C4,"Yes","No")


So, I have 2 tables in Access, i just want to create a query which lookup Account number from in Table 1 in Table 2 and return a Yes if the account number is present in Table 2 and No if Account Number is not present in table 2. Just like above excel function.

 

[sheetspread]

You can use dlookup or an inner join with a calculated column

 

[excel_1317]

You can use dlookup or an inner join with a calculated column

Inner join would only return common Account Numbers but I want all Account Numbers from Table 1 with a Yes or No populated against them.

How just Dlookup wil work? I already tried with no success.

 

[xenou]

Can't help editing your formula:
=IF(ISNUMBER(MATCH(C4,$A:$A,0)),"Yes","No")

 

[xenou]

Use a left join of table 1 on table 2.

 

[excel_1317]

Can't help editing your formula:
=IF(ISNUMBER(MATCH(C4,$A:$A,0)),"Yes","No")

I am trying to achieve same result in Access like this formula does in excel.

 

[xenou]

your formula only looks up a single value. Are you trying to test a single value for existence?

 

[sheetspread]

Look here:

Microsoft Access tips: Getting a value from a table: DLookup()

Look up the CompanyName field from table Company, where CompanyID = whatever your destination table ID is:

=DLookup("CompanyName", "Company", "CompanyID = " & [CompanyID])

or for text e.g.:

=DLookup("CategoryName", "Cat", "Category = '" & [Category] & "'")

 

[excel_1317]

your formula only looks up a single value. Are you trying to test a single value for existence?

If I drag the formula then it will look for values in C5, C6....C50 and just return a Yes or No

 

[xenou]

You want to write a Query using Left Join.

Select 
	Table1.AccountNumber, 
	IIf(IsNull(Table2.AccountNumber), "No", "Yes") AS MyOtherColumn
From
	Table1
	Left Join Table2
	On Table1.AccountNumber = Table2.AccountNumber

Note that depending on what your ultimate aim is, you can also use the unmatched records wizard, although personally I prefer to write my own SQL.