Look-up return

  • Thread starter Thread starter Mac
  • Start date Start date
M

Mac

I am trying to get multiple returns for an item :
if I try to look up "Apple" and the possibl results beiing
looked up are on another worksheet and want the 2ns column
returned
Col A Col B
Apple Juice
Apple Pie

How do you get it to return Juice in a cell and Pie in
another?
Also, if the data is on more than 1 sheet in a workbook,
how can you look up an item in the entire workbook, not
just sheet 1 or sheet 2?
Thank you!
 
This is a time when autofilter really works good, instead of creating a
complex formula I'd suggest
you take a look at data>filter>autofilter or even advanced filter.. A
formula might look like

=INDEX($B$1:$B$10,SMALL(IF($A$2:$A$10="apple",ROW($A$2:$A$10)),ROW(1:1)))

entered with ctrl + shift & enter

Note that the index range starts with the first row, that is because the row
part will always start from row 1 and you either change the range in index
or subtract the number of cell from the start of the row formula to the
first row..

copy down until you get a #NUM error
 
Mac said:
I am trying to get multiple returns for an item :
if I try to look up "Apple" and the possibl results beiing
looked up are on another worksheet and want the 2ns column
returned
Col A Col B
Apple Juice
Apple Pie

How do you get it to return Juice in a cell and Pie in
another?
Click to expand...

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=VLookups("Apple",Sheet6!A1:B2,2) array entered.
Also, if the data is on more than 1 sheet in a workbook,
how can you look up an item in the entire workbook, not
just sheet 1 or sheet 2?
Thank you!
Click to expand...


What's on more than 1 sheet? Where?

Alan Beban
 
-----Original Message-----


If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=VLookups("Apple",Sheet6!A1:B2,2) array entered.



What's on more than 1 sheet? Where?

Alan Beban

Thank you both - Alan - I have a LOT of data, all
Click to expand...
formatted the same on sheet 1,2,3 and 4.
I have info on our products, so product XXX has sales of
ZZ to ABC - there are TOO many items/custoners for a
sheet, so it was carried over to multiple sheets - if I am
working in workbook A and want to look up item SYG in
workbook B, on ANY pages - how can I have the formula look
up on ALL sheets?
Thanks!
 
=IF(ISERR(vlookups("Apple",[B.xls]Sheet1!$E$1:$F$2,2)),IF(ISERR(vlookups("Apple",[B.xls]Sheet2!$E$1:$F$2,2)),IF(ISERR(vlookups("Apple",[B.xls]Sheet3!$E$1:$F$2,2)),IF(ISERRvlookups("Apple",[B.xls]Sheet4!$E$1:$F$2,2),"",vlookups("Apple",[B.xls]Sheet4!$E$1:$F$2,2)),vlookups("Apple",[B.xls]Sheet3!$E$1:$F$2,2)),vlookups("Apple",[B.xls]Sheet2!$E$1:$F$2,2)),vlookups("Apple",[B.xls]Sheet1!$E$1:$F$2,2))

Alan Beban
 
Back
Top