Lookup Multiple

  • Thread starter Thread starter MIKE0W
  • Start date Start date
M

MIKE0W

I cannot figure this one out maybe someone can help. I need to return a
value based on two previous knowns.

In B2 I have a list and can choose either 5, or 15
And then in B3 I can choose from a variety of sizes 1, 8, 6, 14 (sizes
are common for 5, or 15)
In B4 I want it to go to sheet two in the size column and find the
appropriate and then go to the appropriate 5, or 15 column and return
the data than corresponds with that.

Example
B2 – 5
B3 – 8
B4 – looks in sheet two size column and finds the 8 and then in the
weight column for 5 and finds 0.233 and returns that result.

Or

B2 – 15
B3 – 6
B4 - looks in sheet two size column and finds the 6 and then in the
weight column for 15 and finds 0.355 and returns that result.

Any help is appreciated. Thanks.

Mike
 
Hi Mike try this.

Assuming you have a look up table like this on sheet 2

col A ----- colB ----- col C
---5----------1---------0.1 e.g
---5----------8---------0.2
---5----------6---------0.3
---5---------14--------0.4
--15---------1---------0.5
--15---------8---------0.6
--15---------6---------0.7
--15--------14--------0.8

If you have your validated list in row 2 with 5 or 15
and you have a validated list in row 3 with 1, 8, 6, 14

In B4 put the following

=INDEX(IF(B$2=Sheet2!$A$1:$A$8,Sheet2!$C$1:$C$8),MATCH(B$3,IF(B$2=Sheet2!$A$1:$A$8,Sheet2!$B$1:$B$8,0)))


you need to enter the formula using control + shift + enter.

HTH.
 
Try...

=INDEX(ResultRange,MATCH(1,(WeightRange=WeightCriteria)*(SizeRange=SizeCr
iteria),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
Back
Top