Well, look. First of all: If you wish to forcefully use "Excel" (as mentioned in your original question and the comments) you will be in the need to use it via WINE, which may decrease the speed on how do you do this task and certain functions (like the vertical and/or horizontal lookup functions) may not work as expected.
I suggest you to give a chance to LibreOffice Calc, which handles both Excel (.xls and .xlsx) and LibreOffice (.ods) file formats. This way you can share your document with MS (Windows) based system users and work with the data without a major issue in any direction.
Now, let's cook fast food.
Let's say we have a spreadsheet with the details of your customers, as shown in the next small table called "Clients" (see the name at the bottom of the spreadsheet).
Now, let's rename "Sheet2" and use the "Search Form" name for it. After which we'll setup certain cells in order to contain the desired data as follows:
Now, having in mind that we have a "vertical list" where the data is having a column name and rows containing the details of each record, we'll use the LibreOffice's VLOOKUP function in order to search for the desired data and bring the results on the corresponding cells, as follows.
In the C7 Cell of the "Search Form" spreadsheet, (which will correspond to the "State" Data), we put: the next formula:
=VLOOKUP(C5,Clients.A2:G7,2)
By Default we'll see a #N/A
error message in the cell but it is caused by the NULL
data in the "City" cell (C5
), we'll solve this issue by writing something in the C5
Cell, something like "San Antonio
" and the form will deliver the right State in the proper cell as shown in the next screenshot.
Now, in a different recipe, we'll use the same data but this time we'll sort the columns based on a different order as shown in the next screenshot:
I will explain later the need to change the order on the columns. For now let's focus on the creation of the new search form formulas in order to bring the data in the right place.
Now, to look for the "Name" field, we write the next in the C14 and C16 cells respectively:
C14: =VLOOKUP(C12,Clients.A2:E7,4)
C16: =VLOOKUP(C12,Clients.A2:E7,5)
This will give the next results when looking for "Portha, Joseph":
Please note that -nevertheless- the data in the first form (the search state based on city form), are wrong this time.
This is because the VLOOKUP function is using the first column data in order to retrieve the proper results. If we translate the formulas we say that:
In the first example:
=VLOOKUP(C5,Clients.A2:G7,2) means:
Match the data contained on C5 in the vertical array of Clients from A2 to G7 and show me the data in the column number 2.
In the second example:
=VLOOKUP(C12,Clients.A2:E7,4) means:
Match the data contained on C12 in the vertical array of Clients from A2 to E7 and show me the data in the column number 4
Now, this can be useful for practicing some VLOOKUP exercises in order to learn how to create a customized search form.
If you wish to download the .ods file that I used for this exercises just click right here.
Now, if you need to run simple search solutions you may wish to use the "Filter" options in the "Data" Menu of LibreOffice. To do this simply go to the spreadsheet where the data resides, make sure it includes headers (titles for the columns) and hit the "Data" Menu, then "Filter" next "AutoFilter". Your column names will get a small button with an arrow pointing to downside. If we hit any of these small icons we'll see useful filtering data in order to separate the data in a reduced group which will contain only the desired data as seen in the next screenshots:
Auto Filter buttons
Auto Filter options for the State Column selecting only the Texas State
Results of choosing only the Texas State in the drop down filter options. Please note that the small icon for "State" is now blue, indicating filtering on this criteria
If you wish to do some additional search, you can run a "Standard Filter" from the options on any of the drop down auto filter buttons and you will see something like this:
In this dialog window you can use as many filter criteria as you need in order to keep only the desired data, remember that filter criteria values shows a drop down menu with a selection of the values in the table.
I hope this help you to solve your issue.
Final words: If you insist on using Microsoft Excel remember that the VLOOKUP/HLOOKUP and Standerd Filter, the same as other functions works the same both in LibreOffice and Microsoft Office and there is no problem when converting, unless you experience some kind of issue with translations, lets say: In Spanish we use BUSCARV/BUSCARH and other spanish words to create the formulas.
Good luck!