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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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]).
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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
Back
Top