Index and Match

jo.stanley

Board Regular
Joined
Apr 13, 2004
Messages
177
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
Code:
=IF(AND(A1=Shift!A1,B1=Shift!B1,Shift!D1="current"),Shift!C1,"change this")
 
Upvote 0
this is my best guess
Code:
=IF(OR(AND(Shift!A1="Current",Shift!B1="Current",Shift!D1="Current"),AND(Shift!A1=A1,Shift!B1=B1,Shift!D1="Current")),Shift!C1,"")
 
Upvote 0
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.
 
Upvote 0
try this


Excel 2013/2016
ABCD
1BF526Supplier1Failure
2BF527Supplier2Current
3BF410Supplier3Failure
4BF429Supplier4Failure
5BF426Supplier5Current
6BF511Supplier6Current
7BF58Supplier7Failure
8BF510Supplier8Current
9BF424Supplier9Current
10BF428Supplier10Current
11BF516Supplier11Failure
12BF46Supplier12Current
13BF419Supplier13Current
Shift



Excel 2013/2016
ABC
1BF511Supplier6
Return
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top