formula for multiple variables.

T

Tacklemom

I am trying to come up with a formula that incorporates muliply variables. I
know that formula for 2 variable that I have used is:

=if(a7=304,vlookup(a9,screens!$a$27:$h$37,3,false),vlookup(a9,screens!$a$27:$h$27,4,false))

I want the A9 to be constant as that is the cell that tells the formula the
column to look up. The second variable is material and I have 8 options and
eight columns set up is there a way that my senerior it has to equal ( eg
size 60 and material 304) and I tell it the cell to look up based on those
two numbers/data being met??
 
J

Jim Thomlinson

Changing up the reference so that A9 is a constant just involves adding the $
signs to make the reference absolute

=if(a7=304,vlookup($a$9,screens!$a$27:$h$37,3,false),vlookup($a$9,screens!$a$27:$h$27,4,false))

Try this formula for getting the offset to work out

=vlookup($a$9,screens!$a$27:$h$37,match($A$7,screens!$B$26:$h$26, 0) + 1
,false)

The match function returns a number correspoinding to where in the list the
match was found.
 
T

T. Valko

See if this gives you an idea...

When looking up 2 variables a typical table would have one variable along a
vertical axis and the other variable along a horizontal axis. Like this:

...........A..........B..........C
1....................X..........Y
2........1..........5...........3
3........2..........4...........7
4........3..........6...........2

X and Y are one variable. Let's assume these are "sizes". 1,2,3 are the
other variable. Let's assume these are "products".

Assume you want to lookup the price for product 2 for size Y.

E1 = 2 (product 2)
F1 = Y (size Y)

=VLOOKUP(E1,A1:C4,MATCH(F1,A1:C1,0),0)

Result = 7
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top