Excel search

Hi Vivek,

Please note that you have to use two techniques, one for highlighting, and one for lookup.

IngeborgHawighorst has explained the first one.

But you don't have to create a helper column, you can directly put that formula into the Conditional Formatting rule.

With regards to the second one, you can depend on VLOOKUP function, with the support of COLUMNS function as follows:

=VLOOKUP($E2,$A$2:$C$1000,COLUMNS($A$1:B$1),0)

Where:

$E2 is the lookup value;

$A$2:$C$1000 is the lookup range;

COLUMNS($A$1:B$1) is column index number;

0 represents FALSE which is turn on the exact match.

NOTE: I've used COLUMNS function in column index number argument to make the column index number expandable as you drag the formula to the right so that you don't have to change it manually for each column.

I've updated the workbook with the solution, please find it in the attachments.

Hope that helps

Haytham