Automated VLookup Formulas with Two Parameters

[kpark91]

This is a question I want to ask :) (I think this post might be a bit long)

I am using XL2003 in Windows Vista Enterprise

So, I have two tables.
One is called Reference Table
and the other is called Result Table.

(This is a much simpler version of what I need to do)

Reference Table: (This table is in the worksheet, "Reference", top-left corner being in A1 and the whole table is defined as name "REFR")

	A	B	C	D
1	Param1	Param2	Result1	Result2
2	1	4	20	1
3	1.5	8	34	5
4	3	8	57	4
5	4	9	21	3
6	10	8	53	7
7	10	1	97	52
8	30	8	10	68

Result Table: (This table is in the worksheet, "Result", top-left corner being in A1)

	F	G	H	I
1	Param1	Param2	Result1	Result2
2	1	4
3	1.5	8
4	3	8
5	4	9
6	10	8
7	10	1
8	30	8

As you can see one table is full of values
and the other one only has parameters1 and 2 filled in with different orders.

Now, I want to be able to 'lookup' the Reference Table and fill into the according rows.

So, the result table would be like this:

	A	B	C	D
1	Param1	Param2	Result1	Result2
2	1.5	8	34	5
3	4	9	21	3
4	10	1	53	7
5	30	8	10	68
6	10	8	53	7
7	3	8	57	4
8	1	4	20	1

It looks fairly simple, however here is the catch.
I need to use a formula, not VBA and refrain from using IF statements if possible
Regardless of the order or number of rows or columns, it must input the correct values into the according record of data using/checking Param1 and Param2


I've learned how to achieve that with VLOOOKUP
but it would only compare one parameter.

This is my formula in C2 (Result1) of Result Table:

Code: Select all

=VLOOKUP($A1,REFR,MATCH(C$1,OFFSET(REFR,0,0,1),0),FALSE)

To quickly explain my formula,
It looks up the same value in A1 of the Result table in Reference table then it matches the exact titles and returns whatever is beneath the matched title.
It works fine but the problem is..
whatever it is looking up, it only returns the first value it finds in the reference table.

So instead of Correct Table shown below

	F	G	H
1	Param1	Param2	Result1
2	1	4	20
3	1.5	8	34
4	3	8	57
5	4	9	21
6	10	8	53
7	10	1	97
8	30	8	10

It returns The Errorenous Table shown below

	F	G	H
1	Param1	Param2	Result1
2	1	4	20
3	1.5	8	34
4	3	8	57
5	4	9	21
6	10	8	97
7	10	1	97
8	30	8	10

I know I've rambled on but could anybody help me?

Thank you very much,

Kevin :)

[HansV]

In cell C2 on the Result sheet, enter the following array formula (i.e. you must confirm it with Ctrl+Shift+Enter instead of just Enter):

=INDEX(Reference!C$2:C$8,MATCH($A2&"|"&$B2,Reference!$A$2:$A$8&"|"&Reference!$B$2:$B$8,0))

Please note the use of mixed absolute/relative references.
Fill right to D2, then down to C8:D8, or vice versa.
Sample workbook attached below:

Sample.xls

[kpark91]

Wow. That was awesome.

However, I have no idea what you did..

Could you explain please?

Moreover, how can I use names? (REFR)
I've defined the table as REFR si I tried subbing
Reference!C$2:C$8 with
OFFSET(REFR,0,2,0,1) but nothing :(

[HansV]

In order to look up two values at once, I concatenate them: $A2&"|"&$B2 concatenates the values in columns A and B with a vertical line | in between; the vertical line is used to make the values distinct - without that line, 34 concatenated with 5 would yield 345, exactly the same as 3 concatenated with 45; with the line, 34|5 and 3|45 are different.

Reference!$A$2:$A$8&"|"&Reference!$B$2:$B$8 does the same for columns A and B on the Reference sheet. We want to concatenate each corresponding pair of cells, that's why it needs to be an array formula.

MATCH($A2&"|"&$B2,Reference!$A$2:$A$8&"|"&Reference!$B$2:$B$8,0) searches for the concatenated values within the concatenated columns, looking for an exact match and returning the index of the row where it is found.

This index is used to look up the desired value in another column.

If you name the entire table on the Reference sheet REFR, you can use this array formula in C2 on the Result sheet:

=INDEX(INDEX(REFR,0,3),MATCH($A2&"|"&$B2,INDEX(REFR,0,1)&"|"&INDEX(REFR,0,2),0))

Explanation: INDEX(REFR,0,2) is the second column of REFR.

Modified sample attached.

Sample2.xls

[kpark91]

Thank you very much Hans.

You just saved me from my headache :)

[HansV]

I actually suspected that these formulas would give you a headache...

[rory]

For a non-array version:
=LOOKUP(2,1/((INDEX(REFR,0,1)=$A2)*(INDEX(REFR,0,2)=$B2)),INDEX(REFR,0,3))