• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 42
  • Last Modified:

VLOOKUP type forumla needed

I have an Excel spreadsheet with two tables. The first table contains a log of data, in which there is no unique key, the second table is a list of unique keys and the most recent entry from the first table.

Table 1 - Assignment History
Columns:-
Date
Serial No.       - Device Serial Number, can appear multiple times.
Device Type   - Phone, Tablet, or Other
Action              - Assign or Unassign
Administrator - Name
Server               - Server A or Server B

Table 2 - Uniqueu Device List
Columns:-
Serial No.       - Device Serial Number, can appear multiple times.
Device Type   - Phone, Tablet, or Other
Last Action Date     - Date of last action (Currently this is a formula -
=MAX(IF(Assignment_History[Serial No.]='Unique Device List'!A2,Assignment_History[Date]))

Open in new window

Last Action              - Should be Assign or Unassign, looked up from Table 1, based on Serial No. and the Last Action Date.

My question is what excel formula can I use to lookup the Action from Assignment History and populate this in my Last Action Column in Table 2 using both Table 2's Serial No. and Last Action Date?
0
Terry Rogers
Asked:
Terry Rogers
  • 2
  • 2
1 Solution
 
Mike in ITIT System AdministratorCommented:
Please provide a workbook with some sample data that we can test against.
0
 
KoenChange and Transition ManagerCommented:
i think the attached file gives you the solution...

I turned assign/unassigned into 1 and 0 (in a column that could be hidden), to be able to use a sumproduct formula...
EE2.xlsx
0
 
Terry RogersIT Senior EngineerAuthor Commented:
Hi Koen,

We use VBA to manipulate and populate some of the day, so adding helper columns isn't possible.

Ive attached some randomised data.
0
 
KoenChange and Transition ManagerCommented:
Terry,

no attachement... :-(

anyways use the attached formula

notice it is an array formula : enter it and hit CTRL+SHIFT+ENTER
EE2.xlsx
0
 
Terry RogersIT Senior EngineerAuthor Commented:
Hi Koen,

That worked perfectly, thank you for your help.

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now