Forum Discussion
Excel search
Hello,
in the following screenshot, I moved the list to column F and created a helper column in column D with the formula
=IF(COUNTIF(F:F,A2)>0,TRUE,FALSE)
in cell D2 and copied down.
Then I selected the whole table from row 2 to the bottom and created a new conditional formatting rule with the formula
=$D2
I selected a yellow fill and confirmed all dialogs.
Hi Haytham,
Thanks for your response!
Please see the attached excel.
I have abridged my table here with ~1000 rows and 3 columns.
I need to find the 20 IDs highlighted in column 4 from column1.
How to use the index/match/vlookup in this case?
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