## Searchable List Using Formula

### Using Excel Formulas – IF, SEARCH, VLOOKUP, IFERROR

We Can Search Result with Name or Code. Trace Customer Balance or Any Criteria.

Find any result using any searching criteria.

Ex. In the following Image looking 1st List is Creditors Balance & second List is Search list for search creditors name & his Balance.

In the Cell G3 Type Name & Get Result that contains Text.

#### Steps:-

First we are creating search code Before List column. This code use for

**VLOOKUP**Result.For create Code Use formula

**IF Formula, COUNT Formula, SEARCH Function**in one cell before list inserting column.Insert Column.

In the cell

**A6**Type**=IF(COUNT(SEARCH(**Then Select Search cell G3 & press F4 for Locking Cell & Press Comma

Click on cell

**C3.**Which contain Name & close with 2 Bracket )) & pres comma.Then Select Cell A5 & Type plus sign & one

**+1**press comma & click cell A5 Again.Close the bracket & press Enter.

Copy Formula & paste till data end .

**=IF(COUNT(SEARCH($G$3,C6)),A5+1,A5)**

Means If we are type text in cell G5. search formula match text with C5 cell. IF Text contain match with any text then code generate with +1 value else not change.

When text match automatic code generate & we will use for

**VLOOKUP**Criteria.In the Following Image when G5 text match with column C then next code Generated.

### VLOOKUP

Click on cell G6. Apply

**Vlookup**using Code.For

**vlookup**Need Criteria & Range & Column Index Number.Here Criteria is 1,2,3 Code.

Range is From Code to End Data column.

Column Index number is Number of column from Code to Name & Amount.

Type

**=VLOOKUP(**& click on the cell F6 & press comma.Then Select range from the code Column A to Last column Balance that is D Press comma.

Then Press 3. Because Name are are available in the 3 number column from the code.

Then Press comma & Press 0

Close the Bracket & Press Enter.

**=VLOOKUP(F6,A:D,3,0)**

Copy formula & Paste in the cell below.

We will found Names as per search cell.

Use **Vlookup Formula** Again for the Creditors Balance. Criteria & Range are same. Column Index number is 4 instead of 3.

But Here 2 Names are Available & Remaining Result shows

**#VALUE!.**For Remove these error we are using Formula

**IFERROR.**In the formula cell Press F2 & after = sign Type **IFERROR(**

Then click where ending the formula press comma & Type “” (double inverted comma)

Means if getting the error “”(double inverted comma) will show blank result.**Cell G5 =IFERROR(VLOOKUP(G6,A:D,3,0),””)****Cell F5 =IFERROR(VLOOKUP(F6,A:D,4,0),””)**