VLookup with excel multiple columns

[cubstyled]

I have a work book all data validation is on sheet1 and sheet2 holds two combo boxes that validate against each other through vba. I am trying to do a vlookup that validates to 2 named ranges incompasing 2 columns per name. I want to do this as aposed to puting everything in just two columns and naming the whole range OpRate to keep my sheet from being thousands of lines.
=Sheet1!$C$1:$D$116 ----- Named DneedleRate
=Sheet1!$E$1:$F$35 -------Named ZigZagRate

I want column "E" on sheet two to vlookup c2 and validate against the named ranges
I currently have all data in two columns named OpRate and it is getting out of hand ( length) and I am using (this is working, just to stinking long for records)

=IF(ISNA(VLOOKUP(C2,OpRate,2,0)),"",VLOOKUP(C2,OpRate,2,0))

There will eventualy be about 15-20 named ranges I would like to vlookup against

Thanks for any and all help

 

[SkipVought]

Hi,

I don't understand why you do not want your data in 2 columns unless you are running out of rows (65,536 row maximum)

But short of just wanting to, I'd ask this question. Do you have a common lookup value for more than one of your ranges?

And Exactly what IS your question??? Please post some representative data for us to work with.


Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[cubstyled]

Reason: easier readability for the static data
Question: How do I create a vlookup that will look at 2 different named ranges? I can do one named range but I will be eventually using many.
I'm not sure what you mean about common lookup value -- Forgive me I'm not very well versed in advanced excel.
What kind of data would you like.

Thanks for your help!

 

[SkipVought]

You want to do a lookup with multiple criteria?

Pivot Table, DGET, SUMPRODUCT, SUMIF, COUNTIF, -- kinda depends on exactly what you're after, which is why I asked you to...

"Please post some representative data for us to work with."

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[bluedragon2]

Why do you not put your data in one area and then for your static looks, reference which data you want where. This will make life easier for all functions down the road.

[Blue]Blue[/Blue] [Dragon]

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

 

[cubstyled]

I hope this is what you want

Sheet1
Column A name range "Group"
DNeedle
ZigZag
CleanMerrow

Column C name range "DNeedle"
2N DOUBLE NEEDLE
2N01 2N AH & LEGS
2N08 2N SET STITCH
2N080001 2N SET STITCH

Column D is included with Column C in name range "DNeedleRate"
.25
.35
.45
.55

Column E name range "ZigZag"
ZZ ZIG ZAG
ZZ08 ZZ TRIM TO 8"
ZZ080001 ZZ TRIM UP TO 8"
ZZ16 ZZ TRIM TO 16'

Column F is included with Column E in name range "ZigZagRate"
1.25
1.35
1.45
1.55

Sheet2
Coulmn B Validate List and ComboBox
Column D Validate List and ComboBox

Column E or F I want to do a VLookup to auto populate the rate which is the second column of "DNeedleRate" and "ZigZagRate" ( there will be more of these later)

I can use if all of my rates are in one name range but I want multiple
=IF(ISNA(VLOOKUP(C2,OpRate,2,0)),"",VLOOKUP(C2,OpRate,2,0))


If you want me to past the VBA for the ComboBox I will

Bottom line I want to auto populate one column F or E from multiple Name Ranges

Thanks again--- If this what you wanted, Great - if not please be more specific on what you need from me.

 

[SkipVought]

You are making a fundamental mistake by chopping up similar data into separate tables. Both DNeedleRate & ZigZagRate are SIMLAR relations, that are differentiated by a NAME which is a data element.

A typical question I would ask a client is, "what happens when you add another needle type?" You not only have to add the DATA for that type, but also add another TABLE for that type. It is BAD DATABASE DESIGN, which is exactly what a series of tables are in an Excel Workbook are -- a DATABASE. It is grief waiting to strike!

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[cubstyled]

So you are saying keep all operations and rates in the same columns? I am trying to avoid this because I don't want 5000 or more lines. This is for ease of use I'll be creating it but wont be using it. I work with SQL and Access on a daily basis and I am aware of my benifits and lack there of. If in sql or access I would have all of my operations together. But the users who will be using this are just that END USERS (need i say more) and they will be changing the formulas to suit there needs. I am trying to make this as easy for them to work with as possible. They will be the ones changing the static data and entering in the rest of the dynamic data on a daily basis, I am just setting up the back end. So my question to you is do you have an answer for me or are you going to keep telling me something I already know? I obviously already know the obvious I showed you that in my previous posts, what i don't know is how to acomplish what I want. Other wise i wouldn't have posted my question in the first place.
I wasn't loking for DB design I was looking for a way to vlookup against multiple NameRanges.

 

[01Patrik]

Perhaps this is what you want - but it is limited to two search criterias.

Public Function VLOOKUP2(Search1 As Variant, Table As Range, ResCol As Integer, Optional Search1Col As Integer = 1, Optional n As Integer = 1, Optional Search2 As Variant = False, Optional Search2Col As Integer)

'   Like VLOOKUP, but you can search within any column (Search1Col) in the Table.
'   The function also includes an optional second searchvalue (Search2) in an optional
'   second searchcolumn (Search2Col). N:th row (1st as default) fulfilling both criterias is returned
'   The function is limited to EXACT searches, no RANGE_LOOKUP as in VLOOKUP.
'
'   Note that you can use this function instead of the normal VLOOKUP as well since N, SEARCH2, SEARCH2COL are optional.



Dim i As Integer
Dim iCount As Integer

    For i = 1 To Table.Find(what:="*", searchdirection:=xlPrevious).Row
        If Table.Cells(i, Search1Col) = Search1 Then
            If Not Search2 = False Then
                If Table.Cells(i, Search2Col) = Search2 Then
                     iCount = iCount + 1
                End If
            Else
                iCount = iCount + 1
            End If
        End If

        If iCount = n Then
            VLOOKUP2 = Table.Cells(i, ResCol)
            Exit For
        End If
    Next i
End Function

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[SkipVought]

What is so magic about 5000 rows limit? Have you ever heard about the [End] key for getting to the start/end of a contriguous list in two/two keystrokes?

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[cubstyled]

Patrik ,
thanks I'll give that a shot

 

[cubstyled]

Skip,
You must be a programmer! never taking in account that end users are not techies and what's with the attitude not all programmers are gods and I posted my question to get an answer not get flamed by someone who obviously does know the answer or just can't find an answer

 

[SkipVought]

cub,

For the last 10 years, I have observed and BAILED OUT users who made workbook application that were poorly designed. They start out [sub]small[/sub] and then grow and often become UNMANAGEABLE because of the bad design.

Data normalization is not an instinctive design approch that users would inherantly know and understand. It is something that I have had to learn. I guess I am playing the role of mom/dad -- "Some day, when you grow up, you'll understand, MAYBE".

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[pbrodsky]

I'm with Skip.
The fact that you are designing in Excel rather than Access doesn't change the basic principles of which designs are easy to work with and which become difficult when the application grows. Excel has many tools for utilizing data stored as a simple table (sorting, autofilter etc.) and arbitrarily breaking data into many small tables will probably hurt in the end. You would almost certainly do better in the long run with 1 large table with appropriate category fields.

 

[cubstyled]

Thanks to all who have posted. You aren't telling me anything I don't already know. The reason for this was the operations will never change only the rates and that will be twice a year. I was trying to make things easier for those changes for
non tech users, so when they get the updates that there was less chance for data entry mistakes. I wasn't thinking of stream lining i was thinking of the end user getting pages of green bar and having to enter in the rates. I have had it working the correct way (two columns one name range and it does work great) But I didn't want to have to cleanup after the end users.
Skip sorry I came across as an Ass but I felt I was being flamed for not doing it your way( and I do understand your way which is by far the correct way) But as far as the grow up, UNCALLED FOR!!! I explained that i knew, but i was looking for an alternative.

Once again Thanks for all input

CubStyled

 

[pbrodsky]

How will these rate changes happen?

It might be possible to work something out so that end users don't need to do manual entry at all.

("... the end user getting pages of green bar and having to enter in the rates")

 

[SkipVought]

If you wnat to make it user friendly, then you write a user interface that looks up the correct record base in the user's input criteria and updates the row as required. -- SOP for inquiry/add/change/delete!

Sorry for the misunderstanding about "Some day, when you grow up..." It was meant to be an analogy to advice from parents that we all rejected, to some degree, in our youthful naivety.

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[cubstyled]

I was looking to do a dump but it looks like it will be the old fashion way. There are multiple reports that our VP runs in his costing and operational runs for our floor control (multiple calculations from two different systems -- i know it sucks!). Unfortunately this part of the system is not done on the sql server it is on a RS6000 program writen in ProIV and Cobol. This is changing (will be on SQL)just not happening fast enough.
Skip are you saying create a form for the adds, changes, and deletes and let the user enter that way? That way they never see the actual table so it doesn't matter how it is structured? Then I can still use the combobox and the original vlookup statement that looks at the OpRate name range which consists of two columns and all operations and rates ie...
ZigZagRate and DNeedle? If this is correct, instead of working off of two worksheets, they would work off of a form and one worksheet.

Cubstyled

 

[SkipVought]

yup!

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[pbrodsky]

What about the possibility of you scanning and OCR'ing the neccessary reports and creating a combination spreadsheet/update program that would update the rates?

Or having an additional (simple) sheet for user input (if it's absolutely neccessary to have user input) and a vba program to transfer the data to the correct table?

There are many possibilities for keeping the design simple and still making it easy for users - don't give up too easily.