# Finding Data in a cell by looking for 3 variables

#### Guzzlr

##### Well-known Member
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:

### 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
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
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?

#### DanteAmor

##### Well-known Member
Could you give an example of how your data is?
It could be with a macro.

#### Guzzlr

##### Well-known Member
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.

 F S Y 20 Plan Key Finish 458 ABC POP 3-7-2019 623 HII POP 4-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
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
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
• 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
• 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

##### MrExcel MVP, Moderator
Do you only ever want to get the date for ABC & POP?