Finding Data in a cell by looking for 3 variables

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
742
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
 
Last edited:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,177
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Apr 20, 2009
Messages
742
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

Well-known Member
Joined
Dec 3, 2018
Messages
10,177
Office Version
2007
Platform
Windows
Could you give an example of how your data is?
It could be with a macro.
 

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
742
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.

FSY
20PlanKeyFinish
458ABCPOP3-7-2019
623HIIPOP4-9-2019

<tbody>
</tbody>




<tbody>
</tbody>
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,494
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Apr 20, 2009
Messages
742
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.
Code:
=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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,494
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Apr 20, 2009
Messages
742
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,494
Office Version
365
Platform
Windows
Do you only ever want to get the date for ABC & POP?
 

Forum statistics

Threads
1,089,185
Messages
5,406,689
Members
403,103
Latest member
mkirchner

This Week's Hot Topics

Top