Excel 2000: (&) Operator in VLOOKUP will not work 1

[LINSEY]

My main spreadsheet(STYLE) has a style column, version column, styleversion column, and many data columns that follow:

STYLE[tab]VERSION[tab]STYLEVERSION[tab]DATA[tab]DATA[tab]DATA[tab]

The STYLEVERSION column uses an (&)operator to combine style and version columns.

On a separate sheet, I have created a form where the style and version are entered and the needed data is populated from the main spreadsheet(STYLE).

I used the following formula to lookup the data (where style is entered into cell I2 and version is entered into cell I3):

LOOKUP(I2&I3,STYLE!C:C,STYLE!D)

BUT this formula will return values for versions that do not exist (looks up the closest value).

SO, I tried a VLOOKUP formula:

VLOOKUP(I2&I3,STYLE,4,0)

The (&)operator in this formula doesn't work. I get a #N/A value every time.

Please help!

Linsey

 

[xlbo]

VLOOKUP certainly does work with &'s - I'd be royally scr*$ed if it didn't. You probably have trailing spaces in your data - hence the reason the lookup only found a near match - try:
VLOOKUP(trim(I2)&trim(I3),STYLE,4,0)


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[LINSEY]

Geoff,

Still doesn't work.

Does the VLOOKUP look for the data to match (I2&I3) in the whole array, or just the first column?

Maybe if I move the STYLEVERSION column to the first column of the spreadsheet it will work?

Any thoughts?

 

[Adnane]

Hi,

if i understood you right, you need to get the data field based on the concatenantion of the Style field and the Version field.

here's what i did and it worked just fine for me

Style Version StyleVersion Data Data Result Formula
Sty le1 Style1 1 1 1 =VLOOKUP(A2&B2,C2:E4,2,FALSE)
Sty le1 Style1 2 2 2
Sty le3 Style3 3 3 3

Hope it helps!

Adnane

http://www.microsoft.com/northafrica

 

[DaleWatson123321]

LINSEY,

Here's a formula that works - I've tested it. It uses the DGET function that is appropriate for your situation IF the records in you data list will always be UNIQUE - in terms of combining the STYLE and VERSION fields.

With the DGET function, you don't require the extra field "STYLEVERSION".

Here are the steps to set up the formula.

1) On your STYLE sheet, assign a range name to all the columns your data occupies (I've used the name "data&quot in the example formula).

2) On your separate sheet where you've set up your form, assign a range name to I2 - I've used "style_inp", and assign a range name to I3 - I've used "vers_inp" in the formula below.

3) In an "out of the way" location, and enter this formula:
=AND(STYLE=style_inp,VERSION=vers_inp). When you enter this formula, it will display as: #NAME?. This is normal in this siutation.

4) Assign a range name (I've used the name "crit" - short for "criteria&quot to 2 cells - the cell with the formula entered in Step 3, and the cell immediately above it.

5) Optional: For reference I usually enter a label to the left of the range name assigned for the criteria - in this case enter the label "crit" to the left of the 2 cells you've named "crit".

6) Now you're ready to enter the DGET formula. Naturally you can enter it whatever location you prefer.
=DGET(data,"DATA1",crit)

IMPORTANT: For the DGET database function (and other database functions like DSUM, DCOUNTA, etc) to work, they require that the field names in your data list (or "database&quot are UNIQUE. In your example, you used DATA, DATA, DATA. I'm sure you meant this to mean different fields of data, and that the ACTUAL field names are different (unique).

In the formula, I've used "DATA1", but you'll need to change this to whatever field you want to pull your data from.

I hope you've been able to follow these steps. If you have any difficulty, let me know. And again, I can always email you the file.

Please appreciate that by using the DGET function, this can "open the door" to a whole new and POWERFUL component of Excel that too many Excel users have overlooked. Excel's database functions are EXTREMELY POWERFUL - in that they can easily accommodate whatever simple or COMPLEX criteria are required in order to isolate the precise data you require for any part of your application.

These "database functions" are related to Excel's "Advanced Filter" - see the menu: Data - Filter - Advanced Filter. The same type of criteria as described for the above DGET function can be used by the Advanced Filter component to either: a) Filter data in place, or b) Extract data (a copy) to a separate sheet. This ability to extract data selectively to a separate sheet, can be VERY useful in generating various type of reports where you want to provide the "detail" to support summary reports. And of course the database functions (DSUM, DCOUNTA, etc), using the same criteria, can be used to provide "Summary" reports - showing just the "totals". The summary data can include whatever type of breakdown you require.

Perhaps I got a "little carried away", but unfortunately there is not very much documentation on this subject, so I feel obliged to help "fill the gap" created by Microsoft.

I hope this helps. (Let me know if you require more.)

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

I think ADNANE has the right answer here - I assumed that STYLE was a named range covering the styleversion field and the value fields. For vlookup to work, the data you are looking up must be in the far LEFT side of the range you are looking IN

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[LINSEY]

Dale,

The DGET function sounds like it will help in case I need to add columns in the middle of the spreadsheet later.

I named my main spreadsheet "STYLE", I2 = "STYLEINPUT", I3 = "VERSIONINPUT".

When I tried the formula "AND(STYLE=STYLEINPUT,VERSION=VERSIONINPUT)", it returns value "TRUE" instead of #NAME?. Not sure if that is a problem?

I named that cell and the one above "CRIT".

Then I entered "DGET(data,FAMILY,CRIT)" in my form. (FAMILY is the header for the column I wanted data from.) It returns #NAME?.

Was "data" in the formula supposed to be a reference to something?

Linsey

 

[LINSEY]

OK...I went back and read more carefully...data in your formula equals STYLE in mine. BUT, that doesn't work either.

HELP!

 

[DaleWatson123321]

LINSEY,

It sure would be MUCH simpler if I could email you the file.

There's no need to post your email addresss here on Tek-Tips.

And you don't have to worry about emailing me. MANY others from Tek-Tips have been doing so for a couple of years now, and NONE have reported any problems.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[LINSEY]

Wow, I need to learn to read more carefully! I put my column name (FAMILY) in quotations and everything works great now!

Thanks a lot Dale!

 

[xlbo]

LINSEY - have you tried ADNANE's response ???


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[LINSEY]

Geoff,

Yes, putting the STYLEVERSION column to the far left of the worksheet solved the problem, BUT if I need to add columns to the middle of the main spreadsheet, I would have to change all the formulas on the form.

Dale,

I appreciate your offer to look at my file. It's working great now, but I'll keep that in mind in the future.

Linsey

 

[Adnane]

I don't see why you're saying that you have to add a column in your spreedsheet?? you said that its format was
STYLE VERSION STYLEVERSION DATA DATA DATA

My formula is based in this format.

Maybe i misunderstood???

Adnane

http://www.microsoft.com/northafrica

 

[LINSEY]

Adnane,

I am building a large database (over 100 columns with a potential of thousands of rows of data) that will be used by many people. Once I get everything set up and people start using the database, I expect that some people are going to want information added to the database. For ease of data entry, it would be nice to be able to insert new columns of data where appropriate instead of at the end.

Hope that explained why I may need to add columns.

Linsey

 

[KenWright]

Just out of interest, did you try the formula I gave you in your other thread, that didn't require you to create a concatenated field, but did it on the fly within the formula itself.

Only downside to the array formula, is that it can use up scarce resources if the sheet is very big.

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

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

 

[LINSEY]

Ken,

I did not try the formula you suggested in the other thread, as I had already solved the problem with the help of the other posts. I originally added a field to my spreadsheet using the (&)operator to combine the two cells. I have since deleted that field and have used Dale's suggestion of the DGET function, which is working great.

Thanks for taking the time to try and help with my problem.

Linsey

 

[KenWright]

No problem, and appreciate the feedback.

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

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