lookup function?

**saucy_dee** · 02-26-2008, 03:15 PM

Hi

I have one spreadsheet with a list of produce, price etc. I would like to put yes in column A for some of the items (that we wish to order) and then on another sheet have it pull all the items (that have yes in column A) along with the name and price of each. If column A, cell ? is blank then it doesn't pull row of data in.

See below for an example of the main sheet

Yes Broccoli 14 bunch $27.00
Broccoli Crowns 20 lb $33.00

Can I do this with a function or is it a pivot table or something else!

Thanks for your help!

D

**NBVC** · 02-26-2008, 03:55 PM

Please see attached.

formula in Sheet2!A1:

=IF(ROWS($A$1:$A1)>COUNTIF(Sheet1!$A$1:$A$100,"Yes"),"",INDEX(Sheet1!B$1:B$100,SMALL(IF(Sheet1!$A$1:$A$100="Yes",ROW(Sheet1!$A$1:$A$100)-ROWS(Sheet1!$A$1)+1),ROWS($A$1:$A1))))

copied down as far as necessary and across as far as necessary.

Note: This is an array formula and was confirmed with CTRL+SHIFT+ENTER not just ENTER. The { } brackets appear when confirming this way.

Adjust ranges to suit in Sheet1!A1 and confirm using the CSE keys and then copy down and across.

**saucy_dee** · 02-26-2008, 04:02 PM

Thanks for your help! I really appreciate it. Been going cross eyed trying to look through the help to find a solution but when you don't know what it's called it's almost impossible.

Will give your solution a go and let you know how I get on!!!

Thanks again

**saucy_dee** · 02-26-2008, 04:28 PM

Thanks again. Had a few issues to start with changing the ranges but managed to work it out!

I can't thank you enough!

**NBVC** · 02-26-2008, 04:31 PM

Great, thanks for the feedback!

Thread: lookup function?

Thread Tools

Display

lookup function?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions