Excel 2000: How to "Lookup" Based on Criteria from 2 Columns 1

[LINSEY]

I'm having trouble looking up information from a database based on criteria from 2 columns.

Here's the basic layout of the database:

STYLE[tab][tab]VERSION[tab][tab]A/I[tab][tab]TRIAL
LYW[tab][tab][tab]000[tab][tab][tab][tab]I[tab][tab][tab]T111
LYW[tab][tab][tab]001[tab][tab][tab][tab]I[tab][tab][tab]T112
LYW[tab][tab][tab]002[tab][tab][tab][tab]A[tab][tab][tab]T113
MTS[tab][tab][tab]000[tab][tab][tab][tab]I[tab][tab][tab]T114
MTS[tab][tab][tab]001[tab][tab][tab][tab]I[tab][tab][tab]T115
MTS[tab][tab][tab]002[tab][tab][tab][tab]I[tab][tab][tab]T116
MTS[tab][tab][tab]003[tab][tab][tab][tab]A[tab][tab][tab]T117
MTS[tab][tab][tab]004[tab][tab][tab][tab]I[tab][tab][tab]T118

I'm trying to create a spreadsheet where I type in the "STYLE" and a formula gives me the "TRIAL" for the active version (an "A" in the "A/I" column designates active).

I want to be able to lookup over 100 styles in one spreadsheet. It will look similar to below where I type in "STYLE" and "TRIAL" values are returned:

STYLE[tab][tab]TRIAL
LYW[tab][tab][tab]T113
MTS[tab][tab][tab]T117

Please help!

Thanks,
Linsey

 

[xlbo]

You need to create a new column that concatenates the 2 together (in your database)
you can then use a construct like:

assuming new column is col C
TRIAL is in col D
you are entering the STYLE in A2 on sheet1 and the database is on sheet2

=vlookup(A2&"A",sheet2!$C$2:$D$100,2,false)



Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[LINSEY]

Thanks for the reply Geoff. I'm trying to avoid concatenating, because it may cause other issues if I add a column to my current database. Does anyone have another suggestion???

linsey

 

[bluedragon2]

DGET function

[Blue]Blue[/Blue] [Dragon]

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

 

[KenWright]

Using your data and assuming they are in Cols A/B/C/D and that your lookup value is in B12.

=INDEX(D29,MATCH(B12&"A",A2:A9&C2:C9,0))

array entered using CTRL+SHIFT+ENTER - You will get curly braces around the formula if entered correctly.

Assumes only one possible match for any Active Trial

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[LINSEY]

Ken,

I've been trying that formula over and over. It gives me "#VALUE!" every time. There is only one possible match. I can't figure out what's wrong. Can you help???

linsey

 

[KenWright]

Are you 'array entering' it and seeing the curly braces when done?

Hit CTRL+SHIFT+ENTER at the same time to array enter it.

It will look this in the formula bar if you get it right:-

{=INDEX(D29,MATCH(B12&"A",A2:A9&C2:C9,0))}

You cannot just enter these braces manually - it will not work, you must array enter it.

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[LINSEY]

Ken,

When I do the CTRL+SHIFT+ENTER I get the curly braces but it gives me "#NUM!" now!!

linsey

 

[KenWright]

Hmmm - works fine for me with your example data. Have you tried it with just the data you posted to see if it works then? If so then something else in your data is causing it to crash, but at the moment I'm struggling. I've tried it in Excel 2003 / 2002 and 2000 and it works fine in all with the data you posted, first time every time. If you had the wrong ranges you would likely just get wrong answers, and if you had accidentally referenced a blank range or there was no match you would get #N/A.

I just can't seem to get a #NUM no matter what I do.

I have your data in the cells A19 exactly as you have them shown, with row 1 being the headers, and I never used the headers in the formula though it wouldn't have mattered if I did. I have the value MTS or LYW etc in cell B12 and I have put my formula in A12 though I could put it anywhere except within the ranges it is querying.

I array enter the formula and it works every single time I try it. I change the value in B12 from MTS to LYW or vice versa and I get the correct Trial value in A12.

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



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[LINSEY]

Ken,

Thanks so much for your help! I was defining my array as the whole column (including field name). When I changed it to just the info, it worked!!

linsey

 

[tf1]

If you are using Excel 2003, you can replace A2:A9 and D29 (in the above exapmle) with the Range Names Style and Trial: this is because Excel 2003 is smart enough to assume that the ranges Trial and Style are the Columns under the Column Headings of those names. When you later add or remove data, the named ranges automatically compensate.


Regards: tf1

 

[KenWright]

Ahhhhhhhhh - That was it - You cannot use a full column reference in an array formula. The field names won't hurt as long as all your ranges are equal in size, but you need to either specify a slightly smaller range (One cell smaller would have done) or define the ranges automatically to cater for more data being added. If you want to do the latter then just holler

tf1:- I think the title of the post excludes that option for the OP.

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------