VBA/Function to search criteria and return list of value in another sheet.

[floragray]

Hi All,

Could you please help me with the following(not sure if vba is needed or a regular function would do it):

I have a table like this(with exponentially more data) and on a separate sheet i want to set up a search functionality where in if i enter a specific account number in a cell(for eg A1) excel does a lookup in this table for that number, and for that row, all the headers(rules) of "NOK" values, are listed one below the other.

[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: #4F81BD"]A/c No[/TD]
[TD="width: 64, bgcolor: #4F81BD"]Rule 1[/TD]
[TD="width: 64, bgcolor: #4F81BD"]Rule 2[/TD]
[TD="width: 64, bgcolor: #4F81BD"]Rule 3[/TD]
[TD="width: 64, bgcolor: #4F81BD"]Rule 4[/TD]
[TD="width: 64, bgcolor: #4F81BD"]Rule 5[/TD]
[TD="width: 64, bgcolor: #4F81BD"]Rule 6[/TD]
[TD="width: 64, bgcolor: #4F81BD"]Rule 7[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]123[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[/TR]
[TR]
[TD="class: xl66"]124[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD][/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]125[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[/TR]
[TR]
[TD="class: xl66"]126[/TD]
[TD][/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]127[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[/TR]
[TR]
[TD="class: xl66"]128[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]129[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[/TR]
[TR]
[TD="class: xl66"]130[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]131[/TD]
[TD="class: xl65, bgcolor: yellow"][/TD]
[TD="class: xl65, bgcolor: yellow"][/TD]
[TD="class: xl65, bgcolor: yellow"]NOK[/TD]
[TD="class: xl65, bgcolor: yellow"]NOK[/TD]
[TD="class: xl65, bgcolor: yellow"]NOK[/TD]
[TD="class: xl65, bgcolor: yellow"][/TD]
[TD="class: xl65, bgcolor: yellow"]NOK[/TD]
[/TR]
[TR]
[TD="class: xl66"]132[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DCE6F1"]133[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"]NOK[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[/TR]
[TR]
[TD="class: xl66"]134[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD][/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[TD]NOK[/TD]
[/TR]
</tbody>[/TABLE]


Example:

[TABLE="width: 274"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]A/c Number(Configurable)[/TD]
[TD]Rules NOK[/TD]
[/TR]
[TR]
[TD]131[/TD]
[TD]Rule 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rule 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rule 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rule 7[/TD]
[/TR]
</tbody>[/TABLE]




I am a new excel user and would really appreciate the help.

Thanks a ton!