Index Match Formula

[ai1094]

I am trying to write a regular INDEX MATCH formula but don't know how. Here is what I am trying to do:

(match index) In CTL File (sheet name) - - OptionID = ACT and locid = CELLN82 find LXD in Column T

CTL File is the sheet name, I want to look for (Column I) Option ID = "ACT" and (Column A) Location ID = N82 and return the (Column T) MaxLeaseEndDate(orModelExpDate)


Does anyone know how to write this formula?

 

[AhoyNC]

You mean something like this?
This assumes there is only one unique match.

Excel Workbook
	A	B	C	I	T
1	Loc			ID	Date
2	N24			Test1	6/8/2019
3	N82			ACT	7/2/2019
4	N78			test2	7/20/2019
5	N82			Test3	8/4/2019
6
7	Find	Loc	N82
8		ID	ACT
9
10	Date	7/2/2019
Sheet

 

[ai1094]

I am having trouble understanding it. Let me put it this way:

In Sheet 1 in Cell O82 is where I am inserting the formula.
In the CTL file I have the Location ID, Option ID, and LXD.
In Sheet 1 if Option ID = "ACT" and Location ID = N82 (Sheet 1) then return me the LXD from the CTL file.
Does this make sense?

 

[AhoyNC]

Maybe like this.
If your dates in LXD are actual Excel dates (numeric) then you could use the SUMPRODUCT formula.
Formula in O82 will work if dates are text or numeric.

Excel Workbook
	O
82	7/2/2019
83	7/2/2019
Sheet1

Excel Workbook
	A	I	T
1	Loc ID	Opt. ID	LXD
2	N24	Test1	6/8/2019
3	N82	ACT	7/2/2019
4	N78	test2	7/20/2019
5	N82	Test3	8/4/2019
CTL

 

[ai1094]

It's hard to explain it here, I wish I can show you the workbook. Don't worry I will figure it out. Thank you so much for your help.

 

[AhoyNC]

You could put a boarder around a small part of your file and use copy/paste to paste it to the forum. Are the examples I gave above close to what you have?

 

[Will85]

=+INDEX(T:T, MATCH(1, ("N82"=A:A)*("ACT"=I:I), 0))

This is a CSE formula, enter CTRL+SHIFT+ENTER

It's hard to explain it here, I wish I can show you the workbook. Don't worry I will figure it out. Thank you so much for your help.

 

[ai1094]

The problem with the formula you gave me was you have all the information in one sheet whereas I am referencing a different sheet within the workbook.

You could put a boarder around a small part of your file and use copy/paste to paste it to the forum. Are the examples I gave above close to what you have?

 

[Will85]

Thats fine. Just reference the other sheet, the formula will still work.

If you need assistance on that, i suggest youtube to learn some of the basics of excel.

The problem with the formula you gave me was you have all the information in one sheet whereas I am referencing a different sheet within the workbook.