Check a list and return a value

[2112]

I have a list of product features, in column A, and across the top I have product model numbers. If a product has a certain feature, then I put a cross on the corresponding cell. How can I then, on a separate sheet, run a command that says "list all the models that have an LCD Display"?

 

[Derek]

Hi 2112

Try this:

Assume Sheet3 is your product features sheet.
Put features in A4:A100
Put Model Numbers in B3:Z3
A1 & A2 formula =Sheet4!A1
B1 formula =IF(ISERROR(HLOOKUP(COLUMN()-1,2:3,2,FALSE)),"",HLOOKUP(COLUMN()-1,2:3,2,FALSE))
scroll formula right to column Z
B2 formula =IF(OFFSET(B2,MATCH($A$2,$A$3:$A$100,0),0)="x",COUNT(A$2:$A2)+1,"")
scroll formula right to column Z
Hide rows 1 and 2

Assume Sheet4 is the sheet you want to list all the models that have an LCD Display
Formula in B1 =IF(Sheet3!B1<>"",Sheet3!B1,"")
scroll formula right to column Z

Now type "LCD Display" in Sheet4 cell A1 (type it exactly as it appears in column A of Sheet3)

Hope this gives you some ideas

Regards
Derek
PS (original post, slight error in Sheet3 B2 formula....now amended)
This message was edited by Derek on 2002-03-22 07:51
This message was edited by Derek on 2002-03-22 07:53