Extracting a list with Criteria with no errors if criteria not matched.

[Velven]

I'm not sure if this is possible , if it's not possible please let me know.

Any help will be appreciated.

I'm trying to extract a list from A if B matches " Yes " without showing the errors , completely ignoring the errors.

What I usually use is Index and match to return criteria , but it shows Errors. Trying to skip the error.

This is my List.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1st Jan[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1st Jan[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1st Jan[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2nd Jan[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2nd Jan[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3rd Jan[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3rd Jan[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4th Jan[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]
















Expected Results

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1st jan[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1st Jan[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2nd Jan[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2nd Jan[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4th Jan[/TD]
[/TR]
</tbody>[/TABLE]










Once again thx MrExcel , you've made me so much better in using Excel.

 

[Aladin Akyurek]

Do you mean that (a) the input data contain errors or (b) the processing (creation of output) must not lead to errors?

 

[Velven]

Sorry if the way I put it is confusing , i cant edit back my post to fix it.

Basically Sheet 1 is the Data recorded ,
Sheet 2 is the data I want to extract from Sheet 1.

I want my results to be exactly as Sheet 2.
What formula shall I put it so it only extracts if " yes " matched on Sheet 1 ? and skips/ignores all those " No "

 

[Aladin Akyurek]

I don't think you are answering the question you provoked yourself!...

Let A1:B5 of Sheet1 house the input data.

In A1 of Sheet2 just enter:

=COUNTIFS(Sheet1!A1:A5,"<>",Sheet1!B1:B5,"yes")

In A2 of Sheet2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$2:A2)>$A$1,"",INDEX(Sheet1!$A$1:$A$5,SMALL(IF(1-($A$1:$A$5=""),IF($B$1:$B$5="yes",ROW($A$1:$A$5)-ROW($A$1)+1)),

ROWS($A$2:A2)​

)))

 

[Velven]

Thanks !
I'm sorry for not explaining it well enough as I'm pretty confused myself on what should I ask.

Correction : I want to extract a list of data from my input data , with the criteria "Yes". The result must be shown exactly as my table above on sheet 2.

I have done exactly what you say , but it doesn't seems to work.

Sheet1 A1:B5 will be housing my Input data. ( Actually A1:B8 ).

Sheet2 A1 result comes with a 4
A2 onwards goes to 0.

I'm using Microsoft Excel 2013.

 

[Aladin Akyurek]

Adjust the ranges as they actually are.

Control+shift+enter: Press down the control and the shift keys at the same time while you hit the enter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

 

[Velven]

Yes , I've done so.
Sheet 1

Sheet 2

Did I do things wrongly ?

 

[Aladin Akyurek]

In the formula of A2 is the sheet prefix is missing (my fault)...

In A2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$2:A2)>$A$1,"",INDEX(Sheet1!$A$1:$A$8,SMALL(IF(1-(Sheet1!$A$1:$A$8=""),IF(Sheet1!$B$1:$B$8="yes",ROW(Sheet1!$A$1:$A$8)-ROW(Sheet1!$A$1)+1)),ROWS($A$2:A2))))