if/vlookup in access

Griffith

New Member
Joined
Aug 3, 2009
Messages
32
Hi,

I have 2 tables in my database: table1 and table2

The second table contains 2 columns with materials numbers. They are the same materials, but with a different number.

The first table contains 1 column with materials numbers, but they can be found in the 2 columns in the 2nd table, just not the same for every material.

How can I create a 3rd column in the 2nd table that shows something like IF(nr in column1 can be found in table 1; nr1; else nr in column 2)?
So later when I link the 2 tables I need to link on just that one column.

Thanks in advance!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Erick

Active Member
Joined
Feb 26, 2003
Messages
360
I'm not entirely sure I get what you are trying to do.
But if I am to take a stab at what you are trying to do then from what I can understand, you are trying to look up a value that may be in 1 source (maybe a field in 1 table) and if not, then look up another source (maybe another field in a different table), then I think what you need is a query that FULL JOINs your table, where the 'Material' is equal. And then in this query, Field1 will be your material, Field2 is the potential material number from Table1 and Field3 will be the potential material number from Table2. This full join will give you NULL for either Field2 or 3 where there is no matching record. All you then have to do is create a 4th field that says IIF(ISNULL([Field2]),[Field3],[Field2]).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,559
Messages
5,596,841
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top