Fill a table cell with data from another table based on the adjacent table cell value

[timjo]

Hi Community members,

I am compiling a spreadsheet to list the department meeting schedule for the year, retain records of past meetings, and record statistics for meeting presenters.

Sheet "DC-80 Outlines" contains a table listing talk outlines prepared by my company's WHS department. Several other sheets will reference this sheet.

In the sheet "Schedule Archive", I used a named range for the data validation list in column C. That named range is column A in the "DC-80 Outlines" sheet. I want to insert a formula in column B that fills the talk outline title from the "DC-80 Outlines" sheet based on the value selected from the data validation list in column C.

I tried several formulas but I could not deliver the result required. Many times the formulas displayed the "#SPILL" error. Below is a link to the spreadsheet.
Loading Google Sheets

Any suggestions for a formula or adjustments to the spreadsheet?


Many thanks,
Tim

Department Meeting Schedule - Sansitised.xlsx
	A	B	C	D
1	Number	Title	Safety Category	Safety Sub-Category
2	A01	Title 01	Construction and Maintenance
3	A02	Title 02	Construction and Maintenance
4	A03	Title 03	Construction and Maintenance
5	A04	Title 04	Construction and Maintenance
6	A05	Title 05	Construction and Maintenance
7	A06	Title 06	High Hazard Activities Control	Electrical & Energized Equipment
8	A07	Title 07	Operations
9	A08	Title 08	Construction and Maintenance
10	A09	Title 09	Construction and Maintenance
11	B01	Title 10	Operations
12	B02	Title 11	Operations
13	B03	Title 12	Operations
14	B04	Title 13	Operations
DC-80 Outlines

Department Meeting Schedule - Sansitised.xlsx
	A	B	C	D
1	Date	Theme / Item	Safety Outline Number	Type
2	2 June 2011		B05	Safety Talk
3	7 July 2011		B03	Safety Talk
4	4 August 2011	Safety Talk Misc. 01		Safety Talk
5	8 September 2011		A04	Safety Talk
6	7 October 2011	Safety Talk Misc. 02		Safety Talk
7	3 November 2011		C05	Safety Talk
8	1 December 2011		C03	Safety Talk
9	5 January 2012		H06	Safety Talk
10	2 February 2012		C07	Safety Talk
11	1 March 2012	Safety Talk Misc. 03		Safety Talk
12	5 April 2012		A05	Safety Talk
13	7 June 2012		H03	Safety Talk
14	7 July 2012	Safety Talk Misc. 04		Safety Talk
15	6 September 2012		A04	Safety Talk
Schedule Archive

Cells with Data Validation
Cell	Allow	Criteria
C2:C401	List	=SafetyOutlineNumbers
D2:D401	List	=TalkTypes

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you are looking for?

=XLOOKUP([@[Safety Outline Number]],DC_80_Outlines[Number],DC_80_Outlines[Title],"")

 

[timjo]

Welcome to the MrExcel board!

Is this what you are looking for?

=XLOOKUP([@[Safety Outline Number]],DC_80_Outlines[Number],DC_80_Outlines[Title],"")

Yes, that works. I had used that formula previously but no results were displayed because the cell format was set to TEXT instead of GENERAL

Some users will be accessing this spreadsheet using Excel 2019. Is XLOOKUP compatible with Excel 2019?

 

[Peter_SSs]

If some users do not have the XLOOKUP function then you could use this instead.

=IFNA(VLOOKUP([@[Safety Outline Number]],DC_80_Outlines[[Number]:[Title]],2,0),"")

 

[timjo]

Perfect @Peter_SSs, that worked a treat

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.