lookup function with 2 variables

[jacarter]

I have a list of 3 columns to be used as a lookup range.

I need the result in the 3rd column according to the first and second columns.

Example: For the 6 sets of data as follows:
6,2,180
6,2.5,210
6,3,240
8,3,210
8,3.5,265
8,4,315

Results needed for random inputs:
Input 8,3-->210
Input 6,2-->180
Input 8,3.5-->265
Input 6,3-->240

Any ideas?

 

[Juan Pablo González]

If your data is organized like that you can use DGET or SUMPRODUCT, but if you can organize your data a little different, with one of the variables going across the columns, and the other one across rows, you can use an "ordinary" INDEX/MATCH combination.

 

[fairwinds]

This is one way with Index / match.

Enter formula with Ctrl + Shift + Enter

Book1
	A	B	C	D	E	F	G
1
2	6	2	180		7	5	100
3	7	5	100
4	8	3	500
5	9	10	200
Sheet4

 

[Aladin Akyurek]

If you can't re-organize the data, you can add an additional column to data, created by concatenation and use an ordinary, on-expensive retrieval formula.

Book2
	A	B	C	D	E	F	G	H
1	X	Y	Z	Concat
2	6	2	180	62		8	3	210
3	6	2.5	210	62.5		6	2	180
4	6	3	240	63		8	3.5	265
5	8	3	210	83		6	3	240
6	8	3.5	265	83.5
7	8	4	315	84
8
9
Sheet1

D2:

=A2&CHAR(127)&B2

H2:

=INDEX($C$2:$C$7,MATCH(F2&CHAR(127)&G2,$D$2:$D$7,0))

 

[jacarter]

And the winner is. . .

The using index and match. Either use an array command or create a column and use a normal command.

I don't like using array commands because they are more complex then what the users are used to, so I gave him the option of a "dummy" column or the array comand.

Thanks!

Joe