Selected Answer
Naga
Three things are needed to achieve what you want (from the file you posted elsewhere)...
In the Result sheet of the attached revised file (with search term in cell C1), the VLOOKUP formula to return the Keyboard value to cell C4 is:
=VLOOKUP($C$1,Data!$B$5:$G$9,2,FALSE)
where the range column number 2 in bold needs to change to get the other results (in C5:C9) and the FALSE means an exact value will be returned (or an error if the value is not found).
That could return some rows with a zero entry (which can be filtered out) but you want a "1, 2, 3, 4" count of only the rows with a non-zero value. That can be done in conjunction with the filter. The formula in A4 is:
=COUNTIF($C$4:C4,">0")
and only the part in bold changes as that formula is copied to A5:A9. The count increases 1,2, 3... unless a row containing zero is encountered.
The final bit is to apply a filter (on column C) to hide the non-zero rows. The only problem with that is that the filter does not auto-refresh when the value in C1 is changed. To get around that, there's a small event macro behind the Results sheet. The code is as follows (where the words in red are just comments to guide you, in case you're unfamiliar with VBA):
Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if the change was not to the W.O. Number cell...
If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub
'Otherwise refresh the Autofilter...
Range("A3:C8").AutoFilter Field:=3, Criteria1:=">0"
End Sub
When something is changed in that sheet, VBA sees it as a Workseet_Change event to cells(s) is identies as the Target (if you entered soemthing in C3, it would see C3 as Target). The line beginning
If Intersect... sees if there's an "overlap" between C1 and the cell(s) entered- if not (the intersect is nothing) then nothing happens. Otherwise the line beginning
Range("A3:C8")... applied a filter to the 3rd column of A3:C8 to show only rows where that column is >0.
The nett result is that Results sheet will display only rows with a number under Qty each time you enter something in C1. (The other rows are just hidden)
Hope this helps