Creating a wildcard query for multiple columns of data

I have leveraged information from here before, and am now a proud member. I look forward to contributing to this community. Please see below for my current issue. Thank you.

I would like to create a wildcard query function within a separate worksheet but in the same workbook. I would enter the num/string into the query cell and then the results would list every ROW that contained that wildcard value.

For instance: The table below would hold the data, and then on the worksheet with the query function I would enter Bird, and get back all the rows with Bird in it.


A | B | C

1. Cat | Dog | Bird

2. Bird| Fish| Bat

3. Hat | Bat | Ball

4. Red | Tan | Green


Query: "Bird"

Result: Rows #1 & 2

Cat | Dog | Bird

Bird| Fish | Bat


Query: "Bat"

Result: Row #2

Bird| Fish | Bat


Query: 'b' (not case sensitive)

Result: Row #1, 2, & 3

Cat | Dog | Bird

Bird| Fish| Bat

Hat | Bat | Ball


I greatly appreciate any/all the support you can provide. Please bear in mind that I am in the process of getting the rust off of the VBA skills, but look forward to tackling this. Thank you.

tigeravatar,

This is exactly what I need. Thank you very much for example, I greatly appreciate it. I love the excelforum, thank you for the warm welcome!!!

Im going through your code, and was wondering what would I have to modify to make the macro exact and not wildcard? Having both functions would be very useful for the xls. Thank you.

@Winon...thank you for the finishing touch!

@tigeravatar...thank you, I appreciate it.

...I love this place!

See if this might work...it has a wildcard search capability (*). It works better if there isn't empty rows of data though.