Lookup to return multiple values

[lesg46]

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

 

[Mike H]

Maybe

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

Mike

 

[Niek Otten]

Hi Les,

Look here:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

 

[lesg46]

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

 

[lesg46]

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

 

[lesg46]

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

 

[Pete_UK]

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(Inventory!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

 

[Pete_UK]

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(Inventory!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

 

[Max]

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),ROWS($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
---

 

[lesg46]

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

 

[lesg46]

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]

You're welcome - thanks for feeding back.

Pete

 

[Max]

Welcome. Glad it got you going.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik