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,

If you were searching for A2, the answer would be "Not Found".

If you were searching for B2, the answer would be 20.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[hiker95]

sonofthunder07,


Try:

Excel Workbook
	A	B	C	D	E	F	G
1	A	1	10		Search for:
2	A	2	20			A2	20
3	A	3	30
4
Sheet1

The array formula in cell G2 confirmed with CTRL + SHIFT + ENTER (not just ENTER):

=IF(ISNA(INDEX($C$1:$C$100,MATCH(LEFT(F2,1) & "-" & RIGHT(F2,1),INDEX($A$1:$A100 & "-" & $B$1:$B$100,0),0))),"Not Found",INDEX($C$1:$C$100,MATCH(LEFT(F2,1) & "-" & RIGHT(F2,1),INDEX($A$1:$A100 & "-" & $B$1:$B$100,0),0)))

 

[sonofthunder07]

Thanks for the tip.
Here is the view. If my variable is the value "A2" i.e. cell reference B2 then the answer I am looking for is "20".
Thanks

Excel Workbook
	A	B	C
1	A	1	10
2	A	2	20
3	A	3	30
4	A	4	40
5	A	5	50
6	A	6	60
7	B	6	70
8	B	7	80
9	B	8	90
10	B	9	100
11	B	10	110
12	B	11	120
13	C	11	130
14	C	12	140
15	C	13	150
16	C	14	160
17	C	15	170
18	C	16	180
ASAP Download

Excel 2007

 

[T. Valko]

ADVERTISEMENT

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

Why do you want to concatenate the first two columns?

Let's assume this is your data:

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:45px;" /><col style="width:45px;" /><col style="width:45px;" /><col style="width:45px;" /><col style="width:45px;" /><col style="width:45px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">10</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">20</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">30</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">B</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">40</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">B</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">50</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">B</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">60</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">70</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">80</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">90</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr></table> <br /><br />

The lookup values are in E1 and F1.

Enter this array formula in G1:

=INDEX(C1:C9,MATCH(1,IF(A1:A9=E1,IF(B1:B9=F1,1)),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.

If the value to be returned is numeric and the combination of lookup values is unique then you can use this normally entered formula:

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

If you're using Excel 2007 or later then you can use the SUMIFS function:

=SUMIFS(C1:C9,A1:A9,E1,B1:B9,F1)

 

[sonofthunder07]

Fast work! Thanks, but this solution only works if the values in either columns A or B are single digit.

 

[hiker95]

ADVERTISEMENT

sonofthunder07,

Fast work! Thanks, but this solution only works if the values in either columns A or B are single digit.

Per your original request.


Can we have another screenshot of worksheet ASAP Download containing the cell where you will be entering your variable, and the cell that will be returning the answer?

 

[sonofthunder07]

Biff,
My variable is the value "A2" as a variable I don't have visibility as "A" or "2" only "A2". Thanks.

 

[sonofthunder07]

sonofthunder07,




Per your original request.


Can we have another screenshot of worksheet ASAP Download containing the cell where you will be entering your variable, and the cell that will be returning the answer?

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
ASAP Download

Excel 2007

 

[T. Valko]

Biff,
My variable is the value "A2" as a variable I don't have visibility as "A" or "2" only "A2". Thanks.

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:45px;" /><col style="width:45px;" /><col style="width:45px;" /><col style="width:45px;" /><col style="width:45px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">10</td><td style=" border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">A2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">20</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">30</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">B</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">40</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">B</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">50</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">B</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">60</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">70</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">80</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">90</td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td><td style=" border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; "> </td></tr></table> <br /><br />

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.