Return values from multiple columns based on a list

[Apples52]

I have a criteria list in column H. I am trying to return the values in columns A,B,C,F if a value in column H appears in column F.

I've tried a vlookup to do this, but I don't think that's the right tool and I apparently have it set up wrong.

I'm also ok returning one column at a time if returning all columns adds too much complexity.

Screenshot of where I'm at attached.

 

[LegendaryAxe]

Try =FILTER(A2:F700, ISNUMBER(MATCH(F2:F700, H1:H17,0))

 

[LegendaryAxe]

Sorry, just re-read your post and saw you only wanted columns A, B, C, and F to be returned. Try this instead:

=CHOOSECOLS(FILTER(A2:F22,ISNUMBER(MATCH(F2:F22,H1:H4,0))),{1,2,3,6})

 

[Fluff]

@LegendaryAxe please take more note of what version the OP is using, as 2019 does not have some of those functions.

@Apples52 what version of Excel are you using, as the image shows what looks to be a spill range, which is not in 2019

 

[Apples52]

@LegendaryAxe please take more note of what version the OP is using, as 2019 does not have some of those functions.

@Apples52 what version of Excel are you using, as the image shows what looks to be a spill range, which is not in 2019

Ah, yes should have included that with my question. Using a 365 subscription. Worked, thank you!

 

[Apples52]

Sorry, just re-read your post and saw you only wanted columns A, B, C, and F to be returned. Try this instead:

=CHOOSECOLS(FILTER(A2:F22,ISNUMBER(MATCH(F2:F22,H1:H4,0))),{1,2,3,6})

This worked, thank you so much! I'm always amazed and thankful at how helpful and quick everyone is on here.

 

[LegendaryAxe]

This worked, thank you so much! I'm always amazed and thankful at how helpful and quick everyone is on here.

You're welcome!

@Fluff oof, second time I made that mistake. I'll be extra careful to check the Office Version of the poster I'm replying to from now on.