Vlookup with to parameters

[Amnon Wilensky]

Hi

I am trying to find a value in a table by using two parameters as shown in
the table below:

Diameter Height price

80 30 100

80 60 200

80 100 300

100 30 150

100 60 250

100 100 350

125 30 400

125 60 500

125 100 600



In A12 I want to input the diameter

In A13 the input the height

In A14 to have the result

Example:

A12=80

A13=100

A14 (the result) will give "300"

I tried to use Vlookup combine with Index and match without success.

Any help?

Thanks,

Amnon


--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

[olasa]

Try this:

=INDEX(C2:C10,MATCH(A12&CHAR(1)&A13,A2:A10&CHAR(1)&B2:B10,0))
Confirm the formula by holding down Ctrl and Shift, then hit Enter. (It's an Array formula.)

Hope it helped
Ola Sandström


Example:
http://www.*****-blog.com/archives/c...eet-functions/

[Ron Rosenfeld]

On Sat, 11 Jun 2005 19:54:14 +0300, "Amnon Wilensky" <[email protected]>
wrote:

>Hi
>
>I am trying to find a value in a table by using two parameters as shown in
>the table below:
>
>Diameter Height price
>
>80 30 100
>
>80 60 200
>
>80 100 300
>
>100 30 150
>
>100 60 250
>
>100 100 350
>
>125 30 400
>
>125 60 500
>
>125 100 600
>
>
>
>In A12 I want to input the diameter
>
>In A13 the input the height
>
>In A14 to have the result
>
>Example:
>
>A12=80
>
>A13=100
>
>A14 (the result) will give "300"
>
>I tried to use Vlookup combine with Index and match without success.
>
>Any help?
>
>Thanks,
>
>Amnon

If the columns of your table are NAME'd Diameter, Height and Price, then:

=SUMPRODUCT((A12=Diameter)*(A13=Height)*Price)

will give you the result. However, it will return a "0" if the matches are not
exact. What do you want to do in that instance?


--ron

[Domenic]

Try...

=INDEX(C2:C10,MATCH(1,(A2:A10=A12)*(B2:B10=A13),0))

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

Hope this helps!

In article <[email protected]>,
"Amnon Wilensky" <[email protected]> wrote:

> Hi
>
> I am trying to find a value in a table by using two parameters as shown in
> the table below:
>
> Diameter Height price
>
> 80 30 100
>
> 80 60 200
>
> 80 100 300
>
> 100 30 150
>
> 100 60 250
>
> 100 100 350
>
> 125 30 400
>
> 125 60 500
>
> 125 100 600
>
>
>
> In A12 I want to input the diameter
>
> In A13 the input the height
>
> In A14 to have the result
>
> Example:
>
> A12=80
>
> A13=100
>
> A14 (the result) will give "300"
>
> I tried to use Vlookup combine with Index and match without success.
>
> Any help?
>
> Thanks,
>
> Amnon