I have a spreadsheet where one of the tabs(Buyside) keeps track of the last meeting date and the last person met with a specific company in column A. On another tab(raw meeting) I have a running list of all the meetings for the past 3 years. I want to return the name of the last person met from the raw meeting tab into the buy-side tab.

Work done so far: I sorted it by firm name A-Z, date met. added spaces between each firm name. and used this formula to return the cell of the last person met if there's a blank row.

Tab raw meeting:

I want to do that but match column A (in tab buy-side) to tab A(in raw meeting) and return the last contact (column b in the raw meeting)

Tab Buyside

CodePudding user response：

I don't have enough reputation to comment and confirm - but I am assuming that the Raw meeting sheet has a blank row between each firm (i.e. at the end of the Firm's meeting records, there is a blank row before the start of the next firm's meeting records). Correct?

And you want to match column A (Investor Name) in the Buyside sheet to Column A (Firm) in the Raw Meeting data sheet and return the last value from column B for that firm and return it into column G on Buyside. Is this correct?

If so, and if;

• The raw data sheet will ALWAYS have the most recent meeting for each firm at the bottom but together with the other data for that firm
• The Firm name will always be written exactly the same as the Investor Name
• Sheet Names are "Buyside" and "Raw Meeting"

Then, you can use the following formula Input into cell G2 of Buyside:

=IFERROR(INDEX('Raw Meeting'!B:B,MATCH(A2,'Raw Meeting'!A:A,0) COUNTIF('Raw Meeting'!A:A,A2)-1,1),"Firm Not Found")

What this is doing:

1. Counting the number of times Firm name is found (Using COUNTIF)
2. Finding the first instance of Firm name (Using MATCH)
3. Calculating the position of the last instance of Firm name
4. Returning the value in Column B at the same row number as the last instance of Firm name.
5. Returning "Firm not found" if there is an error during lookup

If this answers your question, please kindly upvote and mark as solved