Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup with to parameters
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 |
#2
|
|||
|
|||
On Sat, 11 Jun 2005 19:54:14 +0300, "Amnon Wilensky"
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 |
#3
|
|||
|
|||
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.dicks-blog.com/archives/c...eet-functions/ -- olasa ------------------------------------------------------------------------ olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760 View this thread: http://www.excelforum.com/showthread...hreadid=378312 |
#4
|
|||
|
|||
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 , "Amnon Wilensky" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Using function results as parameters in another function | Excel Worksheet Functions | |||
Parameters are not allowed in queries that can't be displayed grap | Excel Discussion (Misc queries) | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |