Lookup to return multiple values
 

Hi,
I've searched all previous questions to try to work this out alone, but I've
failed to successfully adapt any of the examples in previous posts.

I have a large inventory that I would like to extract items from, in order
to further manipulate the returned data (otherwise I'd just filter it).

As follows:
A B C
Equipment Serial Location
Computer 123 Floor 1
Computer 456 Floor 2
Hole punch abc Garage
Ruler u3j Floor 1

On a separate worksheet, when I type a location in say B2, I'd like to see
a list of all the items in that particular location.

Thus:
A B
Location: Floor 1

Equipment Serial
Computer 123
Ruler u3j

I've been trying to use all variations of lookup, but appear to be barking
up the wrong tree, as I can't get it to return anything at all!

All help, as always, very gratefully received.
Thanks
Les

Lookup to return multiple values
 

Maybe

=VLOOKUP(B2,Sheet1!A2:C5,2,FALSE)

Mike

"lesg46" wrote:

Hi,
I've searched all previous questions to try to work this out alone, but I've
failed to successfully adapt any of the examples in previous posts.

I have a large inventory that I would like to extract items from, in order
to further manipulate the returned data (otherwise I'd just filter it).

As follows:
A B C
Equipment Serial Location
Computer 123 Floor 1
Computer 456 Floor 2
Hole punch abc Garage
Ruler u3j Floor 1

On a separate worksheet, when I type a location in say B2, I'd like to see
a list of all the items in that particular location.

Thus:
A B
Location: Floor 1

Equipment Serial
Computer 123
Ruler u3j

I've been trying to use all variations of lookup, but appear to be barking
up the wrong tree, as I can't get it to return anything at all!

All help, as always, very gratefully received.
Thanks
Les

Lookup to return multiple values
 

Hi Les,

Look he

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"lesg46" wrote in message
...
Hi,
I've searched all previous questions to try to work this out alone, but
I've
failed to successfully adapt any of the examples in previous posts.

I have a large inventory that I would like to extract items from, in order
to further manipulate the returned data (otherwise I'd just filter it).

As follows:
A B C
Equipment Serial Location
Computer 123 Floor 1
Computer 456 Floor 2
Hole punch abc Garage
Ruler u3j Floor 1

On a separate worksheet, when I type a location in say B2, I'd like to
see
a list of all the items in that particular location.

Thus:
A B
Location: Floor 1

Equipment Serial
Computer 123
Ruler u3j

I've been trying to use all variations of lookup, but appear to be barking
up the wrong tree, as I can't get it to return anything at all!

All help, as always, very gratefully received.
Thanks
Les

Lookup to return multiple values
 

Hi Niek,
I've looked here already (found in a previous post), but it's only got the
description of what to do:
"Look up one value and return multiple corresponding values
The formula to look up one value and return multiple corresponding values
can be stated as this: Identify the row numbers that contain the name
"Ashish" in column A, return the corresponding values from column B, and
remove possible error values."
It doesn't give an example of what the formula should look like. So I'm not
really understanding how to put this together. All the other solutions show
the formula!
Les




"Niek Otten" wrote:

Hi Les,

Look he

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"lesg46" wrote in message
...
Hi,
I've searched all previous questions to try to work this out alone, but
I've
failed to successfully adapt any of the examples in previous posts.

I have a large inventory that I would like to extract items from, in order
to further manipulate the returned data (otherwise I'd just filter it).

As follows:
A B C
Equipment Serial Location
Computer 123 Floor 1
Computer 456 Floor 2
Hole punch abc Garage
Ruler u3j Floor 1

On a separate worksheet, when I type a location in say B2, I'd like to
see
a list of all the items in that particular location.

Thus:
A B
Location: Floor 1

Equipment Serial
Computer 123
Ruler u3j

I've been trying to use all variations of lookup, but appear to be barking
up the wrong tree, as I can't get it to return anything at all!

All help, as always, very gratefully received.
Thanks
Les

Lookup to return multiple values
 

Hi Mike,
Sadly not. Result is #n/a. I think this may well be one of the things I'd
already tried.
I feel this should be a relatively easy nut to crack, yet I seem to be
struggling!
Lesley

"Mike H" wrote:

Maybe

=VLOOKUP(B2,Sheet1!A2:C5,2,FALSE)

Mike

"lesg46" wrote:

Hi,
I've searched all previous questions to try to work this out alone, but I've
failed to successfully adapt any of the examples in previous posts.

I have a large inventory that I would like to extract items from, in order
to further manipulate the returned data (otherwise I'd just filter it).

As follows:
A B C
Equipment Serial Location
Computer 123 Floor 1
Computer 456 Floor 2
Hole punch abc Garage
Ruler u3j Floor 1

On a separate worksheet, when I type a location in say B2, I'd like to see
a list of all the items in that particular location.

Thus:
A B
Location: Floor 1

Equipment Serial
Computer 123
Ruler u3j

I've been trying to use all variations of lookup, but appear to be barking
up the wrong tree, as I can't get it to return anything at all!

All help, as always, very gratefully received.
Thanks
Les

Lookup to return multiple values
 

Doh! Just got it. Having re-read this article for about the tenth time
today, I've finally grasped the fact it's taking you through an explanation
of each bit, before getting to the final answer. It was the 'top of page'
immediately after the description that threw me!
So sorry I'm so dim! I'll work through the example and see how it goes.
Les

"Niek Otten" wrote:

Hi Les,

Look he

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"lesg46" wrote in message
...
Hi,
I've searched all previous questions to try to work this out alone, but
I've
failed to successfully adapt any of the examples in previous posts.

I have a large inventory that I would like to extract items from, in order
to further manipulate the returned data (otherwise I'd just filter it).

As follows:
A B C
Equipment Serial Location
Computer 123 Floor 1
Computer 456 Floor 2
Hole punch abc Garage
Ruler u3j Floor 1

On a separate worksheet, when I type a location in say B2, I'd like to
see
a list of all the items in that particular location.

Thus:
A B
Location: Floor 1

Equipment Serial
Computer 123
Ruler u3j

I've been trying to use all variations of lookup, but appear to be barking
up the wrong tree, as I can't get it to return anything at all!

All help, as always, very gratefully received.
Thanks
Les

Lookup to return multiple values
 

Well, you could just filter the data then copy the visible data to
another sheet then move that sheet to another file.

However, if you want a formula solution then you can create a unique
reference for every entry in your inventory sheet. For example, you
can use this formula in D2:

=IF(C2="","",C2&"_"&COUNTIF(C$2:C2,C2))

and copy this down. You would then have a sequential number tagged on
to each entry in column C. (Actually, you don't need the underscore,
but I like to use it to separate the two parts).

Then in your other sheet you can use a formula like this:

=IF(ISNA(MATCH($B$2,Inventory!$C:$C,0)),"",INDEX(I nventory!A:A,MATCH($B
$2&"_"&ROW($A1),Inventory!$D:$D,0)))

to get the Equipment and just copy it into the next cell to get the
Serial number. Then copy these two down as far as you need.

Hope this helps.

Pete

On Oct 9, 12:00*pm, lesg46 wrote:
Hi,
I've searched all previous questions to try to work this out alone, but I've
failed to successfully adapt any of the examples in previous posts.

I have a large inventory that I would like to extract items from, in order
to further manipulate the returned data (otherwise I'd just filter it).

As follows:
* * * *A * * * * * *B * * * * * * * *C * * * *
Equipment * * * * Serial * * * *Location
Computer * * * * *123 * Floor 1
Computer * * * * *456 * Floor 2
Hole punch *abc Garage
Ruler * * u3j * Floor 1

On a separate worksheet, *when I type a location in say B2, I'd like to see
a list of all the items in that particular location.

Thus:
A * * * * B
Location: * * * * Floor 1

Equipment * * * * Serial
Computer * * * * *123
Ruler * * u3j

I've been trying to use all variations of lookup, but appear to be barking
up the wrong tree, as I can't get it to return anything at all!

All help, as always, very gratefully received.
Thanks
Les

Lookup to return multiple values
 

Well, you could just filter the data then copy the visible data to another
sheet then move that sheet to another file.

However, if you want a formula solution then you can create a unique
reference for every entry in your inventory sheet. For example, you can use
this formula in D2:

=IF(C2="","",C2&"_"&COUNTIF(C$2:C2,C2))

and copy this down. You would then have a sequential number tagged on to
each entry in column C. (Actually, you don't need the underscore, but I like
to use it to separate the two parts).

Then in your other sheet you can use a formula like this:

=IF(ISNA(MATCH($B$2,Inventory!$C:$C,0)),"",INDEX(I nventory!A:A,MATCH($B$2&"_"&ROW($A1),Inventory!$D: $D,0)))

to get the Equipment and just copy it into the next cell to get the Serial
number. Then copy these two down as far as you need.

Hope this helps.

Pete

"lesg46" wrote in message
...
Hi,
I've searched all previous questions to try to work this out alone, but
I've
failed to successfully adapt any of the examples in previous posts.

I have a large inventory that I would like to extract items from, in order
to further manipulate the returned data (otherwise I'd just filter it).

As follows:
A B C
Equipment Serial Location
Computer 123 Floor 1
Computer 456 Floor 2
Hole punch abc Garage
Ruler u3j Floor 1

On a separate worksheet, when I type a location in say B2, I'd like to
see
a list of all the items in that particular location.

Thus:
A B
Location: Floor 1

Equipment Serial
Computer 123
Ruler u3j

I've been trying to use all variations of lookup, but appear to be barking
up the wrong tree, as I can't get it to return anything at all!

All help, as always, very gratefully received.
Thanks
Les

Lookup to return multiple values
 

Another play using simpler, faster, non-array formulas to achieve it

Source data as per original post assumed in Sheet1,
in cols A to C, data from row2 down
where key col = col C (Location)

In another sheet,
Input for location will be in B2, eg: Floor 1
Headers in A4:B4 : Equipment, Serial
In A5:
=IF(ROWS($1:1)COUNT($C:$C),"",
INDEX(Sheet1!A:A,SMALL($C:$C,ROWS($1:1))+1))
Copy A5 to B5

In C5:
=IF(LEN($B$2)=0,"",IF(TRIM(Sheet1!C2)=TRIM($B$2),R OWS($1:1),""))
Leave C1:C4 empty. Select A5:C5, copy down to cover the max expected extent
of source data, say down to C100. Minimize/hide col C. Results sought will be
returned in A5:B5 down, all neatly packed at the top, depending on the input
value in B2.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---

Lookup to return multiple values
 

Hi Max,
Once again you've come to my rescue.
Many thanks indeed.
Regards
Les

"Max" wrote:

Another play using simpler, faster, non-array formulas to achieve it

Source data as per original post assumed in Sheet1,
in cols A to C, data from row2 down
where key col = col C (Location)

In another sheet,
Input for location will be in B2, eg: Floor 1
Headers in A4:B4 : Equipment, Serial
In A5:
=IF(ROWS($1:1)COUNT($C:$C),"",
INDEX(Sheet1!A:A,SMALL($C:$C,ROWS($1:1))+1))
Copy A5 to B5

In C5:
=IF(LEN($B$2)=0,"",IF(TRIM(Sheet1!C2)=TRIM($B$2),R OWS($1:1),""))
Leave C1:C4 empty. Select A5:C5, copy down to cover the max expected extent
of source data, say down to C100. Minimize/hide col C. Results sought will be
returned in A5:B5 down, all neatly packed at the top, depending on the input
value in B2.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---

Lookup to return multiple values
 

Hi Pete,

Your solution works very well too. Thank you for your time.

I didn't want to be filtering and copying as I have very many locations to
do this for, and I'll be needing to this task on possibly a regular basis, so
automating it will save much time.

Regards
Les

"Pete_UK" wrote:

Well, you could just filter the data then copy the visible data to another
sheet then move that sheet to another file.

However, if you want a formula solution then you can create a unique
reference for every entry in your inventory sheet. For example, you can use
this formula in D2:

=IF(C2="","",C2&"_"&COUNTIF(C$2:C2,C2))

and copy this down. You would then have a sequential number tagged on to
each entry in column C. (Actually, you don't need the underscore, but I like
to use it to separate the two parts).

Then in your other sheet you can use a formula like this:

=IF(ISNA(MATCH($B$2,Inventory!$C:$C,0)),"",INDEX(I nventory!A:A,MATCH($B$2&"_"&ROW($A1),Inventory!$D: $D,0)))

to get the Equipment and just copy it into the next cell to get the Serial
number. Then copy these two down as far as you need.

Hope this helps.

Pete

"lesg46" wrote in message
...
Hi,
I've searched all previous questions to try to work this out alone, but
I've
failed to successfully adapt any of the examples in previous posts.

I have a large inventory that I would like to extract items from, in order
to further manipulate the returned data (otherwise I'd just filter it).

As follows:
A B C
Equipment Serial Location
Computer 123 Floor 1
Computer 456 Floor 2
Hole punch abc Garage
Ruler u3j Floor 1

On a separate worksheet, when I type a location in say B2, I'd like to
see
a list of all the items in that particular location.

Thus:
A B
Location: Floor 1

Equipment Serial
Computer 123
Ruler u3j

I've been trying to use all variations of lookup, but appear to be barking
up the wrong tree, as I can't get it to return anything at all!

All help, as always, very gratefully received.
Thanks
Les

Lookup to return multiple values
 

You're welcome - thanks for feeding back.

Pete

On Oct 9, 7:06*pm, lesg46 wrote:
Hi Pete,

Your solution works very well too. *Thank you for your time.

I didn't want to be filtering and copying as I have very many locations to
do this for, and I'll be needing to this task on possibly a regular basis, so
automating it will save much time.

Regards
Les

Lookup to return multiple values
 

Welcome. Glad it got you going.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---
"lesg46" wrote in message
...
Hi Max,
Once again you've come to my rescue.
Many thanks indeed.
Regards
Les