Find records in multiple sheets

This lesson will explain to you how to use query function to search specific record from multiple sheets.

According to our example we have three sheets.These sheets have been named as Main,Sales and Purchase.We have Included search box in "Main" sheet.Please see the following screen shot.

image 01

We have to write query function in cell G5.Once invoice number is entered to F5 cell then query will search matching  invoice number  from Sales sheet as well as Purchase sheet.This query function can be used to search more than two sheets.

I used following query function to search records in a multiple sheets

=QUERY({Sales!$A$3:D;Purchase!A3:D}, "Select Col4 Where Col1 contains '"&F5&"'",0)

Here

• Sales!$A$3:D means the Sales sheet
• Purchase!A3:D means the Purchase sheet
• Col4 means the "Invoice Amount" column
• Col1 means the "Invoice Number" column
• '"&F5&"'" means the cell number of search text box reference in main sheet

                                                                                           image 02

You can search any records from multiple records by following this method.

Source
Link to sample sheet
 https://docs.google.com/spreadsheets/d/1ttQL5ioAhGSHckyTGB7dlhDzxTVj0I5njgOlA76F8vU/edit?usp=sharing