Finding Data in a cell by looking for 3 variables

[Guzzlr]

Hello all.
I have a little issue, that I would normally use VBA, but this time I would like to go formula driven.
Data Locations:

• Date is in L:8
• First Variable is in: L:7 = Finish
• Second Variable is in: J:8 = POP
• Third Variable is in: H:8 = ABC

I wish to lookup the date in L:8, in an entire sheet, using the 3 variables of "Finish", "POP", and "ABC"
So, the 3 Lookup values are ABC, POP, and Finish, to pull the date in L:8

I wanted to use maybe Vlookup, or IndexMatch, but I dont know how to use it with three different LookUp values. Or maybe a combination with the Find formula?

All the data I need, is in columns H:L.
Thanks for the help

 

[DanteAmor]

You can concatenate the 3 necessary variables in another column. I do not know how your data is on the sheet, but assuming that in column H you have variable1, in column J variable 2 and in column L variable 3.
In M2 the formula:
= H2 & J2 & L2
Then you can use Index-Match:

=INDEX(H2:M20,MATCH("FinishPopABC",M2:M20,0),2)

Or

=INDEX(H2:M20,MATCH(l7&J8&H8,M2:M20,0),2)

 

[Guzzlr]

You can concatenate the 3 necessary variables in another column. I do not know how your data is on the sheet, but assuming that in column H you have variable1, in column J variable 2 and in column L variable 3.
In M2 the formula:
= H2 & J2 & L2
Then you can use Index-Match:

=INDEX(H2:M20,MATCH("FinishPopABC",M2:M20,0),2)

Or

=INDEX(H2:M20,MATCH(l7&J8&H8,M2:M20,0),2)

I actually thought about this; unfortunately, the sheet the data is on, and that sheet that i would concatenate, is password protected, and the owner will not give me the password.
Is there another way to find the 3 lookups, to get the data in the forth?
Thank you for your help

 

[DanteAmor]

Could you give an example of how your data is?
It could be with a macro.

 

[Guzzlr]

Could you give an example of how your data is?
It could be with a macro.

I hope this helps visualize.

• The worksheet is about 2,300 rows.
• In this example, Row 20 is the header, and does not change.
• I'm looking for the Finish Date of 3-7-2019, found in row 458
• So, I'm looking for a formula that will find:
  • Plan of ABC in Column F
  • Key of POP in Column S
  • And the Finish date in Column Y as 3-7-2019
• Keep in mind, this is only one set of Plan, Key and Finish to find the finish date of 3-7-2019
• Another set will find a finish date of 4-9-2019, found in row 623, based off of the criteria of HII, POP, and Finish
• Total number of sets is about 15 and the finish date may change. The Key of POP will always be POP.

[TABLE="width: 670"]
<tbody>[TR]
[TD="class: xl69, width: 40"][/TD]
[TD="class: xl69, width: 36"][/TD]
[TD="class: xl69, width: 52"][/TD]
[TD="class: xl69, width: 121"][/TD]
[TD="class: xl71, width: 36"][/TD]
[TD="class: xl73, width: 102"][/TD]
[TD="class: xl71, width: 74"][/TD]
[TD="class: xl71, width: 47"][TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]Plan[/TD]
[TD="align: center"]Key[/TD]
[TD="align: center"]Finish[/TD]
[/TR]
[TR]
[TD="align: center"]458[/TD]
[TD="align: center"]ABC[/TD]
[TD="align: center"]POP[/TD]
[TD="align: center"]3-7-2019[/TD]
[/TR]
[TR]
[TD="align: center"]623[/TD]
[TD="align: center"]HII[/TD]
[TD="align: center"]POP[/TD]
[TD="align: center"]4-9-2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl71, width: 47"][/TD]
[TD="class: xl72, width: 55"]


[/TD]
[TD="class: xl72, width: 60"][/TD]
[/TR]
</tbody>[/TABLE]
Typically I would probably use an Index/Match, but there are the 3 criteria of Plan, Key, and Finish, to find the date of 3-7-2019.
If this is not available in a formula style. I was going to try VBA using a Last Row method, and looping.

Thanks for the help

 

[Fluff]

Maybe something like
=INDEX(Sheet2!Y21:Y2500,MATCH(H8&"|"&J8,Sheet2!F21:F2500&"|"&Sheet2!S21:S2500&"|",0))

This is an array formula & need to be entered with Ctrl Shift Enter, not just Enter.

 

[Guzzlr]

Maybe something like
=INDEX(Sheet2!Y21:Y2500,MATCH(H8&"|"&J8,Sheet2!F21:F2500&"|"&Sheet2!S21:S2500&"|",0))

This is an array formula & need to be entered with Ctrl Shift Enter, not just Enter.

=INDEX(Y20:Y100,MATCH(F20&"|"&S20,F21:F100&"|"&S21:S100&"|",0))

The Header's are in Row 20
The data I'm using is in Row 21 instead of 458 in the example of post #5

• Here is the formula I'm using On the same sheet as the data (I thought I'd try to get it working first on the same sheet)

• My test excel sheet is set up the same as the data example post #5 .

• The formula is in cell D20
• In cell D20, I'm getting an "#N/A" error
• I did use Control Shift + Enter when the formula was entered
• My thinking is the formula would find ABC, and POP in Column's F and S. Then pull the Date of 3-7-2019 in cell Y20

Thanks for the help

 

[Fluff]

In your OP you said

• Second Variable is in: J:8 = POP
• Third Variable is in: H:8 = ABC

This is why the formula is looking at those cells.
If the criteria are not in those cells, where are they?

 

[Guzzlr]

• In your OP you saidThis is why the formula is looking at those cells.

If the criteria are not in those cells, where are they?

Ohh. I made an example sheet in Post #5 . sorry

The header is Row 20 and consist of:

• "Plan" F20
• "Key" S20
• "Finish" Y20

The Data is in Row 21 (under the header)

• "ABC" F21 is the Plan
• "POP" S21 is the Key
• 3-7-2019 Y21 is the Finish date

The formula is in D21 and should find ABC under Plan, then find POP under Key, to pull the date of 3-7-2019 under Finish

Thanks for the help

 

[Fluff]

Do you only ever want to get the date for ABC & POP?