Excel 2000: How can I use 2 fields to lookup info in another field? 1

[LINSEY]

Here's the (very) basic layout of my spreadsheet:

STYLE[tab]VERSION[tab]YARN TYPE
LYW[tab][tab]001[tab][tab][tab]1000
LYW[tab][tab]002[tab][tab][tab]1050
LYW[tab][tab]003[tab][tab][tab]4001
PRM[tab][tab]001[tab][tab][tab]2001
PRM[tab][tab]002[tab][tab][tab]1002
PRM[tab][tab]003[tab][tab][tab]5003

I need to look up the yarn type by style and version. I have tried using lookup and index functions, but I am doing something wrong.

I would like to solve this problem with a function or formula since I have no experience with macros, code, etc.

Please help!
Linsey

 

[DrBowes]

A simple solution would be to concatenate the Style and Version into one cell and use VLOOKUP with this.


You can concatenate the cells by entering =A1&B1 and drag it down. You can then copy this range, then Paste Values over the formulae (under Paste Special).

 

[dan1967]

You could always insert a new column in front of your current column A. In this column enter a formula to combine your STYLE and VERSION data for each row.

=B2&"-"&C2

This example will produce:
YarnCode   STYLE    VERSION    YARN TYPE
LYW-001    LYW        001            1000
LYW-002    LYW        002            1050

When you perform your lookup, reference the new combined STYLE-VERSION column. Remember, though, that the sheet your are performing your lookup in needs to have the same reference code.



Dan.

 

[mbarron]

One way to do this is by using the DGET function.

With your data starting in A1:

STYLE	VERSION	YARN TYPE	style	version
LYW		0001	1000		LYW	0001
LYW		0002	1050		Result	
LYW		0003	4001		1000	=DGET(A1:C7,3,E1:F2)
PRM		0001	2001			
PRM		0002	1002			
PRM		0003	5003

The breakdown of the DGet (and the other database functions)
DGET(your database,column number of information to return,criterea)
or in this case
DGET(from my yard database,return the yarn sytle from column 3,where the Style is LYW and the Version is 0001)




Mike

 

[bluedragon2]

One way to do this is using the DGET function.

Lets assume that your three columns of data are in columns A, B, and C

So in D1 put Style
in E1 put Version
in D2 put the style you want to lookup
in E2 put the version you want to lookup
in F1 put =DGET(A1:C7,3,D1:E2)
the A1:C7 is the range of your table, so adjust to the size of your table

the 3 is column three of the table where we want the value to come from

the D1:E2 is the critera area of what we want to lookup.

So if you have PRM in D2 and 003 in E2, the formula will return 5003. It will change to whatever criteria you enter in those two fields.

Instead of changeing your data table, this gives a nice little 5 cell solution to look up values you want. This will work for even larger tables and lookup criteria.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[MJPPaba]

This works

=LOOKUP(D1,A2:A7&B2:B7,C2:C7)

Cell D1 contains the lookup value.
The & Concatenates the Style with the Version to make one value.

Range C2:C7 contains the result set.

Cheers

Crazypabs

 

[LINSEY]

Thanks DrBowes! Your solution seems to work best for me since my spreadsheet is extrememly large and will continue to grow.

Thanks everyone else for your solutions as well!

 

[KenWright]

Two other ways, assuming your example data is in A1:C7, and that your lookup style value is in F1, and your lookup version value is in F2:-

=SUMPRODUCT(($A$2:$A$7=$F$1)*($B$2:$B$7=$F$2)*($C$2:$C$7))

The second allows you to do the concatenting referred to in the other notes, but without actually using any helper cells:-

Again with your lookup values in F1 and F2:-

=INDEX($C$2:$C$7,MATCH($F$1&$F$2,$A$2:$A$7&$B$2:$B$7,0))

array entered - CTRL+SHIFT+ENTER at the same time

Regards
Ken.............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[KenWright]

Also, given that you said the table will increase, you could set it up so that you never have to change any of your formulas.

With your STYLE data in A1:A7, name the range A1:A8 as STYLE.
With your VERSION data in B1:B7, name the range B1:B8 as VERSION.
With your YARN data in A1:A7, name the range A1:A8 as YARN.

Now also colour cells A8:C8 blue or some other colour (Shrink the row a bit of you want as well)

Then, using the second of the formulas I gave you, change it to:-

=INDEX(YARN,MATCH($F$1&$F$2,STYLE&VERSION,0)) Still array entered.

Now, when you want to insert a new row of data, simply click on the blue cells and do Insert / Rows

This will automatically expand the named ranges STYLE / VERSION / YARN, and you won't need to edit any formulas. the only thing you will need to do is change any lookup values in F1 and F2.

Regards
Ken...................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------