multiple lookup formula question
 

hopefully this won't wrap.

line model
1 750
2 745
4 750

Size Holes model Strokes
578 3 745 48
621 2 745 50
621 3 750 36
631 3 745 52

(the above columns are a-f, just moved so
outlook express doesn't wrap)

in the example i need a formula for,
the 1 for the line number and the 621 for the size are
entered as values in separate columns.

for my test case, i need a formula that can lookup the line number
(1 in this case) get the model number (750 in case) and return
the number of strokes for 621 model 759 (36 in this case) and place
it in the same row as the 1 and the 621.

i thought maybe 2 lookups or and index match formula.

can someone help?

--


Gary

multiple lookup formula question
 

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))


=============
But I'm kind of confused at what is in what column. I don't know what model
column to use.

Gary Keramidas wrote:

hopefully this won't wrap.

line model
1 750
2 745
4 750

Size Holes model Strokes
578 3 745 48
621 2 745 50
621 3 750 36
631 3 745 52

(the above columns are a-f, just moved so
outlook express doesn't wrap)

in the example i need a formula for,
the 1 for the line number and the 621 for the size are
entered as values in separate columns.

for my test case, i need a formula that can lookup the line number
(1 in this case) get the model number (750 in case) and return
the number of strokes for 621 model 759 (36 in this case) and place
it in the same row as the 1 and the 621.

i thought maybe 2 lookups or and index match formula.

can someone help?

--

Gary

--

Dave Peterson

multiple lookup formula question
 

thanks dave, i think this will get me going.

--


Gary


"Dave Peterson" wrote in message
...
Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do
it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))


=============
But I'm kind of confused at what is in what column. I don't know what model
column to use.

Gary Keramidas wrote:

hopefully this won't wrap.

line model
1 750
2 745
4 750

Size Holes model Strokes
578 3 745 48
621 2 745 50
621 3 750 36
631 3 745 52

(the above columns are a-f, just moved so
outlook express doesn't wrap)

in the example i need a formula for,
the 1 for the line number and the 621 for the size are
entered as values in separate columns.

for my test case, i need a formula that can lookup the line number
(1 in this case) get the model number (750 in case) and return
the number of strokes for 621 model 759 (36 in this case) and place
it in the same row as the 1 and the 621.

i thought maybe 2 lookups or and index match formula.

can someone help?

--

Gary

--

Dave Peterson

multiple lookup formula question
 

here's what i ended up with. i used dynamic ranges to shorten the formulas

=INDEX(Plates,MATCH(G23&VLOOKUP(V23,PLine,2),PSize &PModel,0),3)
example:
G23 = 621
V23 = 3

i changed the original order in my 4 column set, but what it equated to was:

matched 621&750 in the 4 column set and returned the 3rd column over, 36

line model
1 750
2 745
3 750


Size model Strokes Holes
578 745 48 3
621 745 50 2
621 750 36 3
631 745 52 3


--


Gary


"Dave Peterson" wrote in message
...
Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do
it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))


=============
But I'm kind of confused at what is in what column. I don't know what model
column to use.

Gary Keramidas wrote:

hopefully this won't wrap.

line model
1 750
2 745
4 750

Size Holes model Strokes
578 3 745 48
621 2 745 50
621 3 750 36
631 3 745 52

(the above columns are a-f, just moved so
outlook express doesn't wrap)

in the example i need a formula for,
the 1 for the line number and the 621 for the size are
entered as values in separate columns.

for my test case, i need a formula that can lookup the line number
(1 in this case) get the model number (750 in case) and return
the number of strokes for 621 model 759 (36 in this case) and place
it in the same row as the 1 and the 621.

i thought maybe 2 lookups or and index match formula.

can someone help?

--

Gary

--

Dave Peterson