Index and Match

[jo.stanley]

Hi

Ill try and keep this as simple as I can. I am trying to lookup information based on three pieces of information.

I have 2 sheets

Shift and Return

On Shift Sheet
A1 = BF
B2 = Port
C1 = Supplier
D1 = Failure

So what I need to try and get a result off is

If A1 and B1 on (Shift) match on (Return) and also D1 on (Shift) is 'Current'

Then return C1 (Supplier) to (Return)

I'm hoping I haven't explained this and made it more complex than it is

If A1&B1&D1=current then c1

 

[Scott T]

Since you said B1 I assume the B2 is a typo. You did not say what should be returned if the conditions are false Change "change this" to what you want to be returned in that case.

=IF(AND(A1=Shift!A1,B1=Shift!B1,Shift!D1="current"),Shift!C1,"change this")

 

[AlanY]

this is my best guess

=IF(OR(AND(Shift!A1="Current",Shift!B1="Current",Shift!D1="Current"),AND(Shift!A1=A1,Shift!B1=B1,Shift!D1="Current")),Shift!C1,"")

 

[jo.stanley]

Hiya

Thank you for your help, I have been out the office for a few days so this is first opportunity to respond.

The problem I have is that I only want it to return a result if D1 is current. Its almost like i'm doing a Vlookup but with multiple criteria. So to try and explain in a better way.

A1 = BF4 or BF5 (There will be multiple rows with the same data)
B1 = between 1 or 30 (and again there will be multiple rows with the same data)
D1 = Failure

There will be multiple rows with failure but for example I only want to find the Supplier (C1) in the 'Shift' Sheet if it is Current.

So in the sheet Return If I have, BF4, 1, Current then it will find the supplier (found only on the 'Shift sheet' which has matching data in the 'Shift' Sheet. I have tried both the formulas suggested but they are returning the wrong results.

 

[AlanY]

try this

Excel 2013/2016
	A	B	C	D
1	BF5	26	Supplier1	Failure
2	BF5	27	Supplier2	Current
3	BF4	10	Supplier3	Failure
4	BF4	29	Supplier4	Failure
5	BF4	26	Supplier5	Current
6	BF5	11	Supplier6	Current
7	BF5	8	Supplier7	Failure
8	BF5	10	Supplier8	Current
9	BF4	24	Supplier9	Current
10	BF4	28	Supplier10	Current
11	BF5	16	Supplier11	Failure
12	BF4	6	Supplier12	Current
13	BF4	19	Supplier13	Current
Shift

Excel 2013/2016
	A	B	C
1	BF5	11	Supplier6
Return

Cell Formulas
Range	Formula
C1	{=IF(INDEX(Shift!D:D,MATCH(A1&"/"&B1,Shift!A:A&"/"&Shift!B:B,0))="Current",INDEX(Shift!C:C,MATCH(A1&"/"&B1,Shift!A:A&"/"&Shift!B:B,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.