Check a list and return a value

2112 · Mar 22nd, 2002, 07:33 AM

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 · Mar 22nd, 2002, 08:40 AM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 22nd, 2002, 07:33 AM	#1
2112 New Member Join Date: Mar 2002 Posts: 1	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"?

Mar 22nd, 2002, 08:40 AM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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 ]