Help with formula

[Addictions]

Hello,

Can somebody tell me formula how to return date from column B in to column F for matching product with status awaiting?


[TABLE="width: 574"]
<colgroup><col span="2"><col span="2"><col><col span="4"></colgroup><tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Prodcut[/TD]
[TD]Date[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]20/04/2018[/TD]
[TD]Done[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]20/04/2018[/TD]
[TD]Done[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]20/04/2018[/TD]
[TD]Done[/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]27/04/2018[/TD]
[TD]Awaiting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]27/04/2018[/TD]
[TD]Awaiting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]27/04/2018[/TD]
[TD]Awaiting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

=INDEX($B$4:$B$9,MATCH(1,(($A$4:$A$9=E4)*($C$4:$C$9="Awaiting")),0))

It's an array formula so Control+Shift+Enter when confirming the formula.

 

[Addictions]

Can I somehow use a whole columns instead of ranges?

 

[lrobbo314]

You can, but I would advise against it. Array formulas are pretty expensive and iterating over hundreds of thousands of empty rows would be pretty inefficient. I would select your data in the first 3 columns, hit Ctrl+T to turn the range into a table, then adjust your formula to...

=INDEX(Table1[Date],MATCH(E3&"Awaiting",Table1[Prodcut]&Table1[Status],0))

This way your range automatically expands and you're not wasting computing power.

 

[Addictions]

Thank you. Another problem I have is column E and F area going to be in another sheet really.
It would be sheet2 column A and B. While the first table stays in sheet1.

 

[lrobbo314]

If you turn your data into a table like I said then it won't matter that you're referencing a range on another sheet. The formula would remain the same and still work.

 

[Addictions]

Im getting #value error with it

=INDEX(Table1[Date],MATCH(Sheet3!A2&"Awaiting",Table1[Product]&Table1[Status],0))

 

[Addictions]

Im getting #value error with it

=INDEX(Table1[Date],MATCH(Sheet3!A2&"Awaiting",Table1[Product]&Table1[Status],0))

ctrl + shift+ enter done it thank you

 

[lrobbo314]

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂