Matching 2 Values and Retrieving a 3rd Value

Glen7187

New Member
Joined
Sep 1, 2005
Messages
13
I have a spreadsheet that looks like this (call it sheet A for now):
SSO CTS Cert Plan vs Actuals_03_16_06_MANDATED CERTS_REVA.xls
ABCDEFGHI
1StatusSource CodeL NameEmployee IDF NameCourse CodeClass NameExp DateComp Date
2LBLUE40579SmithHAC53172RodneySHEC&HCRANE & HOIST OPERATIONS9/14/20029/14/2000
3LBLUE40565SchiefenHACN2546CharlesESCA000858Crane and Hoist Operations (Goleta Only)2/8/20042/8/2002
4DORANGE40568DelapazHACE3512GaleESCA000307Non-Conforming Material (MRB) Indoctrination2/12/20042/12/2001
5LBLUE40579HesseHAC29360PaulSHELAS-ILASER SAFETY - INITIAL2/13/20042/13/2002
6LBLUE40579Grace1027599JamesSHEC&HCRANE & HOIST OPERATIONS2/17/20052/18/2003
7ARED40579Alleruzzo1035494ReginaSHEC&HCRANE & HOIST OPERATIONS8/19/20058/20/2003
SSO Certs DATA



Then I have a sheet B that looks like this:

[Extremely large HTML example removed by admin. ~The HTML Maker add-in is for SMALL samples of your worksheet.]


What I'm trying to do is, the values in Sheet B under the Columns after "Area" will have the date of when the people's certification for the corresponding class is expired. That information comes from sheet A. I'm trying to figure out a formula or a macro where, in each of the cells of sheet B under the classes, the cell will find (in sheet A) the person's employee ID first (col A in sheet B) then see if that person has a matching entry of the class ID. If there is a match, then place the expiration date in that cell, if not, then place an "x". For example, on sheet B, cell G3 would have some type of formula (or maybe a macro can do this) where cell G3 would search sheet A for the employee ID number (in this case it's A3 which is 1036454) then see if it has a matching class ID to G3's class which is CLNRM01, on sheet A it would look under col F for that match, if it finds both the employee ID and the class ID, it will take the value from col H of the same row (the expiration date) and place that date in G3 on sheet B. The thing is, there will be many entries of the same employee ID with different class ID's, like employee 10 can have clnrm01 and shec&h while employee 12 will only have clnrom01. If I can't find a matching class ID for that cell, then i simply put an "x" there. So anyone have an idea how I can do this? I was looking at Vlookup but I don't think that will work because I have to match 2 things at the same time and there will be more than 1 entry for both values... sheet A is going to have the same employee ID for all his 5 classes, so 5 entries of the same employee ID but different class ID's for each, so i didn't think vlookup would check them all, only one... I'm not sure tho... Any help? thank you in advance (hopefully my explanation didn't just confuse more... lol)
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
bump... Perhaps someone could give me some ways to search columns and obtain values using VBA? I'm thinking what I can do is search one column for a value then check another value in that row, if no match, search for the next value in the column and check the other value in the row, etc...
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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