Index match formula

[Belle23p]

Hello everyone,

I would like to ask what formula to use if i want some specific data to be reflecting on another worksheet.

On the uploaded file (Data tab), it shows the status of the flight plan for 2016. What I wanted to get is all confirmed flights to be automatically showing on another worksheet (worksheet tab)

Thank you.

 

[Khalid NGO]

Hi @Belle23p, Good day and welcome to the forum

Try the following {array formula} in Worksheet, cell A2:

=IFERROR(INDEX(Data!$A$3:$D$14,SMALL(IF(Data!$B$3:$B$14="Confirmed",ROW(Data!$B$3:$B$14)-2),ROW(A1)),COLUMN(A1)),"")

{array formula needs to be entered with a key combination of
Ctrl+Shift+Enter instead of just enter}

Copy down and across.

Regards,

 

[Ashhu]

Hi Belle
if you are ok with VBA solution, please find attached sheet. click on Export button from data tab, it will export all completed ones from Data tab to Worksheets tab

 

[Marc L]

Hi !

A VBA solution using an advanced filter needs less than 10 codelines
and without a button within a worksheet event …
See also samples in threads of the appropriate VBA forum.

Edit : needs only 3 codelines in a worksheet event !
​

 

[John Jairo V]

Hi to all!

What about a Pivot Table with a Slicer? See the file to check the provide solution. Blessings!

 

[Belle23p]

Hi @Belle23p, Good day and welcome to the forum

Try the following {array formula} in Worksheet, cell A2:

=IFERROR(INDEX(Data!$A$3:$D$14,SMALL(IF(Data!$B$3:$B$14="Confirmed",ROW(Data!$B$3:$B$14)-2),ROW(A1)),COLUMN(A1)),"")

{array formula needs to be entered with a key combination of
Ctrl+Shift+Enter instead of just enter}

Copy down and across.

Regards,

Thank you Khalid

 

[Belle23p]

Hi Belle
if you are ok with VBA solution, please find attached sheet. click on Export button from data tab, it will export all completed ones from Data tab to Worksheets tab

Thank you Ashhu. I'm still learning the ropes for VBA.

 

[Belle23p]

Hi to all!

What about a Pivot Table with a Slicer? See the file to check the provide solution. Blessings!

Thank you John, I shall review the attached file.

 

[Belle23p]

Hi @Belle23p, Good day and welcome to the forum

Try the following {array formula} in Worksheet, cell A2:

=IFERROR(INDEX(Data!$A$3:$D$14,SMALL(IF(Data!$B$3:$B$14="Confirmed",ROW(Data!$B$3:$B$14)-2),ROW(A1)),COLUMN(A1)),"")

{array formula needs to be entered with a key combination of
Ctrl+Shift+Enter instead of just enter}

Copy down and across.

Regards,

Hello Khalid,

I tried to input the formula indicated, however it seems that it doesn't work on my file. Please refer to the attached.

 

[NARAYANK991]

Hi ,

Use this revised formula :

=IFERROR(INDEX('Raw Data'!B$19:B$30,SMALL(IF('Raw Data'!$C$19:$C$30="Confirmed",ROW('Raw Data'!$C$19:$C$30)-MIN(ROW('Raw Data'!$C$19:$C$30)) + 1),ROW(A1))),"")

This is an array formula , to be entered using CTRL SHIFT ENTER.

Enter this in B19 and copy across and down.

Narayan