V lookup with 2 criteria to return results for multiple columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JenL - 20 Sep 2007 19:40 GMT

I have a complicated master inventory spreadsheet from which I need to
generate shorter reports for items currently in use. My column headings are
as below:

A Product
B Color
C Volume Used
D Manufacturer
E Catalog Number
F Lot number
G Date received
H Expiration Date

However, we may have a given product in several different colors.

All 8 columns need to show up in my report.

I would like to be able to use VLOOKUP (or something else that works) to
find (for example) Product X in green and then be able to drag the formula
to return the rest of the 7 columns as well. If I concatenate first, I can't
drag. If I just link the cells then the report gets jumbled up every time
somebody sorts the inventory sheet. If I have to type in a separate vlookup
formula in each column, I'll still be typing this time next year. I know
this really should be done in Access, but unfortunately, that's not an option.

Any help would be greatly appreciated.

Thanks,
JenL

Reply to this Message

Duke Carey - 20 Sep 2007 19:50 GMT

Your issue description is pretty vague, but it sounds as though advanced
filtering out to get the job done. Have you tried that?

Alternatively, you can use MS Query to quickly and easily extract the data
you need into another worksheet. Nick Hodge has an excellent write up at
this link. Read all the way to the bottom. His technique for pulling data
from an Access database into Excel works just as well to pull data from one
Excel sheet into another, within the same workbook, if so desired.

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

> I have a complicated master inventory spreadsheet from which I need to
> generate shorter reports for items currently in use. My column headings are
[quoted text clipped - 25 lines]
> Thanks,
> JenL

Reply to this Message

JenL - 20 Sep 2007 22:04 GMT

OK, here is a snippet of my actual inventory sheet:

A,B,C,D,E,F
Marker, Fluor,Vol (uL),Company,Cat#,Lot No.,Exp Date
CD3,APC,10,BD,340440,70286,10/31/08
CD3,PerCP,10,BD,340663,83838,4/30/09
CD3,PE,5,BD,347347,61276,7/31/08
CD4,PC5,10,Immunotech,IM2636,18,9/30/08
CD4,PerCP,10,BD,347324,79417,11/30/08
CD45/14,FITC/PE,10,Immunotech,IM1201U,39,11/26/07

And it goes on for many dozens of rows. There are also many more columns to
the right, but that's not data I'm interested in for this purpose.

We regularly run an assay using CD45/14 FITC/PE, CD4PerCP, and CD3 APC. I
would like to have, as a separate worksheet within the same notebook, a
dynamic ("live") sheet that looks like:

Marker, Fluor,Vol (uL),Company,Cat#,Lot No.,Exp Date
CD45/14,FITC/PE,10,Immunotech,IM1201U,39,11/26/07
CD4,PerCP,10,BD,347324,79417,11/30/08
CD3,APC,10,BD,340440,70286,10/31/08

If I use VLOOKUP alone, I have no guarantee that the correct CD3 or CD4 will
be chosen.

But, if I use vlookup for a unique marker
=vlookup(A2,'inventory'!A5:H200,2,0) I can then drag that formula to the
right and the rest of the columns will fill in. I get a sequence that reads:

=vlookup(B2,'inventory'!B5:I200,2,0)
=vlookup(C2,'inventory'!C5:J200,2,0)
etc.

If I add a new column before A and concatenate CD3&APC, I can then use
vlookup to find the proper entry. BUT, I can't drag the formula anymore and
have to type in a separate vlookup formula in each and every column, and for
some of our assays this can get to be 100+ rows long. Hence me still sitting
here typing next year.

Is this less vague now?

JenL

> Your issue description is pretty vague, but it sounds as though advanced
> filtering out to get the job done. Have you tried that?
[quoted text clipped - 36 lines]
> > Thanks,
> > JenL

Reply to this Message