Matching data to return value

[Craig]

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.

 

[Ken Wright]

Why do you need separate worksheets for the reference data for each of the
makes. You are including the Make in Col A of each of those sheets anyway, so
it shouldn't be a problem to put your data on a single sheet, and will certainly
make your job easier. That way you can do this with something like SUMPRODUCT,
or if you don't mind a single extra column (Can be hidden) of concatenated data
on both the RegQList sheet and the reference sheet, then it can be done with a
simple VLOOKUP statement or INDEX/MATCH depending on which side of your data the
concatenated column is. If that sounds of any use then I'll be happy to explain
further.

 

[Guest]

I just set it up that way because it seemed to make the
most sense to keep it organized and able to add and
remove manufacture brands/types from the seperate sheet
later as needed,I would like to tey any ideas that you
might have....THANKS for Helping

 

[Ken Wright]

You'll save yourself a lot of grief if you keep it all on a single sheet. You
can also then use Data / Form on that sheet to add new records as necessary and
be sure that the data is in the right format.

Assuming you have put all your reference data on a single sheet:-

If you create a single extra column on each sheet, ie RegQlist and RefData (My
new name for your single reference sheet), and for the sake of it we'll make it
Col A, but you can hide it later if you like. In cell A2 on RegQlist put
=J2&K2&L2&M2 and copy down. On sheet RefData in cell A2 put =B2&C2&D2&E2 and
copy down.

Select all the data on the RefData sheet including the CG column, which is now
Col G because we have added a column at the start, and name it Database.

You can now simply use the VLOOKUP function on the RefData sheet, to look up the
value in Col A in the table Database and give you what is now the 6th column in
that table, ie the CG value. You would od this by putting the following formula
in any column in the RegQlist sheet in the second row and then copy down (Put it
in AA2 for your example data):-

=VLOOKUP(A2,Database,6,0)

This gives you the Cg for each record. If I read the maths right, then all you
need to do is use that formula inside some basi maths to get what you need, eg:-

Your example
=1.29*(H2+14.7)*VLOOKUP(A2,Database,6,0)

By using the Data / Form method of adding records each time, the Database table
will automatically increase to accommodate extra records. If you don't use this
method then you will have to define the range dynamically, else new records will
not be included without some editing of the formula.