excel - Need sheet 2 to autofill based off of criteria from sheet 1 - Stack Overflow

admin管理员组

文章数量:1417096

Tab 1

Tab 2

I'm looking to autofill tab 2 (named Barrels) based off of information from tab 1 (named Master Entries).
If the division (tab 1, column e), matches the division in tab 2 (column B) AND the event is marked (tab 1, column F - J), fill the Rider Name and Horse Name (from tab 1 columns A - C to tab 2 columns B - D).
If that criteria does not match, move to the next row on tab 2.

I have this formula;

=IFERROR(INDEX('Master Entries'!A3:A250, SMALL(IF('Master Entries'!E3:E250="Novice Sr", ROW('Master Entries'!A3:A250)-ROW('Master Entries'!A3)+1), ROW(A1))), "")

but it only allows for the one criteria.
Is there a way to add the secondary criteria?

Tab 1

Tab 2

I'm looking to autofill tab 2 (named Barrels) based off of information from tab 1 (named Master Entries).
If the division (tab 1, column e), matches the division in tab 2 (column B) AND the event is marked (tab 1, column F - J), fill the Rider Name and Horse Name (from tab 1 columns A - C to tab 2 columns B - D).
If that criteria does not match, move to the next row on tab 2.

I have this formula;

=IFERROR(INDEX('Master Entries'!A3:A250, SMALL(IF('Master Entries'!E3:E250="Novice Sr", ROW('Master Entries'!A3:A250)-ROW('Master Entries'!A3)+1), ROW(A1))), "")

but it only allows for the one criteria.
Is there a way to add the secondary criteria?

• excel
• excel-formula

Share Improve this question Follow edited Feb 2 at 22:36 PCDSandwichMan 2,12011 gold badge1414 silver badges2626 bronze badges asked Feb 2 at 22:32 user29030156user29030156 2111 silver badge22 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

You need to add another condition to the first part of your IF formula, so instead of

'Master Entries'!E3:E250="Novice Sr"

You need something like

('Master Entries'!E3:E250="Novice Sr")*('Master Entries'!F3:F250="x")

=IFERROR(
    INDEX($A$2:$A$249,
    SMALL(
    IF(
        ($C$2:$C$249="Novice")*($D$2:$D$249="x"),
        ROW($A$2:$A$249)-ROW($A$2)+1),
        ROW(A1))), "")

Assuming that on the 'Barrels' sheet 'Novice Sr' is in cell B1 and the number 1 is in cell A3 paste the following into cell B3 then copy over to cell D3 and then, while B3:D3 are still selected, copy down as until you see blanks: =IFERROR(INDEX('Master Entries'!A$3:A$250, AGGREGATE(15,6,(ROW('Master Entries'!A$3:A$250)-ROW('Master Entries'!A$3)+1)/('Master Entries'!$E$3:$E$250=$B$1)/('Master Entries'!$F$3:$F$250="x"), ROW(A1))), "")

Let us know if you have any questions.

本文标签： excelNeed sheet 2 to autofill based off of criteria from sheet 1Stack Overflow