Create IF or VLOOK statement?

[Darrin]

Good Evening and thanks in ADVANCE for looking at my
problem. I am looking for some of the Forum experts to
give me a hand please send me or reply with any
code/equation/idea that makes sense;

I have a workbook that contains 6 worksheets.

1=RegQList
2=289
3=Fisher
4=Grove
5=Sprauge
6=Mooney
(Exactly as typed)

I have in the RegQList hundreds and hundreds ,I think
2700 specific and unique Regulators that I need, no must
calculate, various equations for. What I have figured
out is that if I was smart enough to create a LOOKUP or
smart eqations to find
MAKE- Cell J2/MODEL-Cell K2/SIZE-Cell L2/ORIFACE-Cell M2

on each line in the RegQList then if you could help me
create the code to look at the 5 other worksheets find
the matching MAKE-Cell J2, then within that 1 of 5
sheets match-up MODEL/SIZE/ORIFACE (which on the 5 sheets
is always column A-MAKE/B-MODEL/C-SIZE/D-ORIFACE when
that match is located/solved I want the number known as
Cg which is already in COLUMN E of the same Cell rows as
the matching specific make regulator.

I would like that number returned, or so I wish to place
this lookup equation to place the Cg for each specific
Regulator in AA2 of RegQList. Then with that Cg factor I
can return to my basic Excel skills and create the needed
equation of (1.29) X ( SUM H2+14.7) X (SUM AA2) this
equals Q or Flow. The 1.29 and 14.7 are constant numbers
to calculate Flow.

I am going to play with this all night and morning. SO
if you think that you can help me figure this out I would
appreciate it so much I don't think you can even begin to
understand.

 

[liddlem]

Hi Darrin
I just want to check a few facts before we look at a solution.

I take it that each of these lists is a 'parts' list from a different
supplier.

I am not sure how the ReqQList is used?
Do you type in the request data (Make / Model / Size etc) in the
ReqQList and then want it to find the information on the other 5
worksheets?

OR

Do you need ANOTHER worksheet that will look in all 6 of the worksheets
that you have?

This is what I am thinking. Let me know if will work for you.

One sheet is used to choose the data and display the results from the
other 5 (Or 6) in seperate rows.

Eg.
The 'master' sheet will look something like this.

User input Make | Model | Size |
Oriface
Sheet289 make289 | model289 | Size289 | Oriface289
SheetFisher makeFisher |ModelFisher | SizeFisher | OrifaceFisher
etc
etc

 

[Guest]

The RegQList already lists all the data for each
regulator that I am looking to solve Q for. To solve Q I
need to get the Cg which I have manually loaded per
manufacture.
Each row of the RegQList has a specific regulator listing
location and many other items, but the one thing in
common with them all, as far as a KEY feature that
seperates each regulator, is MAKE/MODEL/SIZE/ORIFACE and
those are also unique items listed in each of the other
sheets. You are correct but I don't need a NEW Master
sheet, I just need to get the (Cg), which is a specific
value to each regulator model size etc, once they match
<MANUFACTURE and RegQList>, then I can create a equation
with the other data contained in the "Master Sheet" which
is already the RegQList, Some MANUFACTURES make 200
variations of a meter and each has a specific Cg. They
just produce many variations that all get into pressure
in and out but each model has a specific Cg represented
in column E of each MANUFACTURE sheet. I need to use
column AA of the RegQlist or anything past AA to bring or
SUM= the actual Cq value from column E where ever
MAKE/MODEL/SIZE/ORIFACE match up from the RegQList and
the correct MAKE (Manufacture sheet).

Thanks for helping me...I will refresh every second to
see if you need additional information.

 

[Guest]

FYI as I reread your comment

Both the RegQList already lists:

MAKE / MODEL /SIZE ORIFACE I need data returned like this

Fisher/ Fg-11 / 2 / 3/4 / 15220.6(Cg) The Cg is where I
would place a code or some request to MATCH UP if a (ANY
of the 5 manufacture names) are found in thanks again I
am double clicking refresh as fast as I can...

 

[liddlem]

OK Great

Now Im not sure that this is the BEST solutions, but i think it ma
have potential.

Because LOOKUP will return a value EVEN if an exact match is NOT found
I first do a 'MATCH' and then if an exact value is found, I do
lookup. (This is the part that i think could be done quicker / easier
but I am not sure how.)

FIRST I would combine all the worksheets together into ONE worksheet.
(This may not work for you ?? - This solution assumes that the 'lookup
formula is on the ReqQList worksheet and the CG value that must b
returned comes from the other 5 worksheets.)

On this new worksheet...
a. Add two extra fields
...One which has the suppliers name - I.E. Fisher, Grove etc etc
...The second field is really just a sequential record number which
will call 'seq'. (sequence)

b. Make a combined field EG Cell AB2 = '=(K2 & L2 & M2 & N2...x2)'
Include the manufacturer name.

c. Name the ranges - This makes it easier when writing the formulae.
For Example: Name the combined range (Column X) as 'MyCombo' and the C
column (E) as 'CG'


On the ReqQList worksheet.
In the column where you want the result (Lets say column 'Y')
Cell Y2 = '=MATCH(X2,Combo)'
Cell Z2 = '=if(Y2 > 0, Lookup(Y2,Seq,CG))

Will this work for you

 

[Krefty]

Liddlem,

Thanks so much for working to help me with this but I am
unclear...Do I put all these NEW codes into various
labeled columns in "A NEW WORKSHEET". I have created a
NEW sheet called Combo.

I put Sheet names as A1,C1,E1,G1,I1,K1 and another column
Seg as B1,D1,F1,H1,J1 where do I insert your suggested
code? I have already placed the RegQList Y2 and Z2 code
into those columns.

I am not clear as to what is meant related to combined
field is that on the (5) worksheets or does that relate to
the New Combo sheet also how do I name ranges? and is that
also related to the NEW Combo sheet or does it get
inserted into the RegQList....Again thanks I will keep
checking back often.