Searching two columns based on a concatenated value of those columns and returning a corresponding value from a third column.

[sonofthunder07]

Hello,
I have three columns of data. I would like to concatenate the first two columns of each row and search that result based on a variable and then return the corresponding data from the third column for the row that matches the variable.
E.g. Values in the rows going down in column 1 are A, A, A, etc., values in the rows going down in column 2 are 1, 2, 3, etc., values in the rows going down in column 3 are 10, 20, 30, etc.. If my variable is "A2" the answer would be "20". I am looking for a single formula solution. Thanks for any help you could provide.
Regards,
Ken

 

[hiker95]

sonofthunder07,

Excel Workbook
	A	B	C	D	E
1	Data set 1	Datat set 2	Data set 3	Variable List	Need formula to return values
2	A	1	10	A6	60
3	A	2	20	B11	120
4	A	3	30	C11	130
5	A	4	40	C16	180
6	A	5	50
7	A	6	60
8	B	6	70
9	B	7	80
10	B	8	90
11	B	9	100
12	B	10	110
13	B	11	120
14	C	11	130
15	C	12	140
16	C	13	150
17	C	14	160
18	C	15	170
19	C	16	180
20
ASAP Download

The formula in cell E2 copied down:

=INDEX($C$1:$C$100,MATCH((LEFT(D2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D2&"1234567890")-1)))&"-"&MID(D2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},D2&"0123456789")),99),INDEX($A$1:$A100&"-"&$B$1:$B$100,0),0))

 

[sonofthunder07]

Book1
*	A	B	C	D	E
1	A	1	10	*	A2
2	A	2	20	*	*
3	A	3	30	*	*
4	B	1	40	*	*
5	B	2	50	*	*
6	B	3	60	*	*
7	C	1	70	*	*
8	C	2	80	*	*
9	C	3	90	*	*
Sheet1

Again, assuming the combination of lookup values is unique.

Array entered**:

=INDEX(C1:C9,MATCH(E1,A1:A9&B1:B9,0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Excellent! That did it. Elegant and simple solution. Thanks so much.
Ken

 

[T. Valko]

Excellent! That did it. Elegant and simple solution. Thanks so much.
Ken

You're welcome. Thanks for the feedback!

 

[sonofthunder07]

Excellent! That did it. Elegant and simple solution. Thanks so much.
Ken

P.S. Is there also a way to sum the results if there are multiple matches to this solution? =INDEX(C1:C9,MATCH(E1,A1:A9&B1:B9,0))

 

[T. Valko]

P.S. Is there also a way to sum the results if there are multiple matches to this solution? =INDEX(C1:C9,MATCH(E1,A1:A9&B1:B9,0))

Try this...

=SUMPRODUCT(--(A1:A9&B1:B9=E1),C1:C9)

 

[sonofthunder07]

Try this...

=SUMPRODUCT(--(A1:A9&B1:B9=E1),C1:C9)

Nice work. Thank you again.
Ken

 

[T. Valko]

Nice work. Thank you again.
Ken

You're welcome. Thanks for the feedback!