Comparing two fields

[cbende2]

Hey guys,

SKIP TO TABLE FOR SIMPLE EXPLANATION:

I'm trying to import a list into my database from excel.

It will be a new table that I would like linked to my main Table1.

Table one has PK1, however, we also store PK2 from another database. PK2 is only used for the other database, it is in my database for simplicity when looking up info. It does not hold a primary key role in my database, it is simply a text field. In the other database it is the primary key role.

Now the problem:

I have an excel spreadsheet with PK2 located in it.

After exporting Table1 from my database, I need to look for PK2 in my database, and add PK1 to the excel spreadsheet where PK2(my database) = PK2(excel spreadsheet)

Table for example:


MY spreadsheet

(A) PK2(excel)	(B) PK1(excel)	(C) PK2(mydatabase)	(D) PK1(mydatabase)
ALLIANCE		ALLIANCE	1
ALLIANCE		BARTH	2
BARTH		CASS	3
COOR		COOR	4
COOR		DEEP	5

I'm looking for a formula that compares column (C) with column (A), and whenever there is a match, populate column (B) with the value in column (D).

So the finished result would look like:

(A) PK2(excel)	(B) PK1(excel)	(C) PK2(mydatabase)	(D) PK1(mydatabase)
ALLIANCE	1	ALLIANCE	1
ALLIANCE	1	BARTH	2
BARTH	2	CASS	3
COOR	4	COOR	4
COOR	4	DEEP	5

Its not too hard to understand. I'm just not sure how to go about doing this process. I had no luck with Vlookup.

I know this is an excel question, however I'm a frequent at this website so I figured I'd give it a shot here!

Thanks!

[ItsMe]

I see an Inline If (iif) statement with an Alias as an argument in your future. I would use this Inline If as a second alias within a query object.

[cbende2]

That was a little over my head, but I'll do some research.

Could you explain a little further?

So something like:

Iif(ColumnA = ColumnC) Update ColumnB to ColumnD Where ColumnA=ColumnC

[ItsMe]

Kinda, yes. There is not a ternary operator in VBA so the VBA answer to a ternary operator is an IIF() function.

https://msdn.microsoft.com/en-us/lib.../gg264412.aspx
Check out the above link and you will see an example that I broke. Here, I illustrate where you might be able to place some alias'

SomeAlias: IIf(TestMe > 1000, AliasMyCalc, SomeOtherColumn)