Locating moving cell value in an array, and returning the date form the top of the array.

[tuddym]

Hey all. First of all, what I am asking may not even be possible in excel. However, I figured it was worth a quick shot to reach out to you all first before throwing in the towel.

I need to find a lookup value in an array/table, and return the value (date) at the top of this table. The trouble I am running into is the location of the lookup value will be changing row/column, and there will be multiple returns of each date.

Ive looked into index/match/match, lookups, V/H lookups, different nested formulas and I am coming up with #N/A - my favorite!

Essentially I have a worksheet that is used for crew cycling/scheduling. I want to be able to move around the production line numbers each crew is working (move dates - left/right) as well as assign to different crews (up/down). I then want my formula to return the date where these lookup values reside (top row of the array).

5/1 6/1 7/1 8/1 Return 1003 - 6/1
Team 1 1001 1003 1005 1007 1004 - 6/1
1008- 8/1... etc,
Team 2 1002 1004 1006 1008

Team 3 2001 2002 2003 2004


This way if I want to swap 1001 and 1007 in the schedule, I want the return file to show 1001=8/1, and 1007 = 5/1. Furthermore, I may swap 1001 and 2003 for example and would need the equation to still find 1001, even in the new row.

Any ideas?

Thanks again!

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this, copied down.
I am assuming that a production number will occur at most once in the table, that the dates are 'real' dates (Numbers) not Text and that you are using Excel 2010 or later.
Formula in H2 is copied down.

Excel Workbook
	A	B	C	D	E	F	G	H
1		5-Jan	6-Jan	7-Jan	8-Jan		Prod #	Date
2	Team 1	1001	1003	1005	1007		1001	5-Jan
3	Team 2	1002	1004	1006	1008		1002	5-Jan
4	Team 3	2001	2002	2003	2004		1003	6-Jan
5							1004	6-Jan
6							1005	7-Jan
7							1006	7-Jan
8							1007	8-Jan
9							1008	8-Jan
10							2001	5-Jan
11							2002	6-Jan
12							2003	7-Jan
13							2004	8-Jan
Lookup Date