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

sonofthunder07

New Member
Joined
Jun 12, 2007
Messages
13
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
 
Last edited:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
sonofthunder07,


Try:


Excel Workbook
ABCDEFG
1A110Search for:
2A220A220
3A330
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)))

 
Last edited:

sonofthunder07

New Member
Joined
Jun 12, 2007
Messages
13
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
ABC
1A110
2A220
3A330
4A440
5A550
6A660
7B670
8B780
9B890
10B9100
11B10110
12B11120
13C11130
14C12140
15C13150
16C14160
17C15170
18C16180
ASAP Download
Excel 2007
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

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

New Member
Joined
Jun 12, 2007
Messages
13
Fast work! Thanks, but this solution only works if the values in either columns A or B are single digit.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

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

New Member
Joined
Jun 12, 2007
Messages
13
Biff,
My variable is the value "A2" as a variable I don't have visibility as "A" or "2" only "A2". Thanks.
 

sonofthunder07

New Member
Joined
Jun 12, 2007
Messages
13
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
ABCDE
1Data set 1Datat set 2Data set 3Variable ListNeed formula to return values
2A110A660
3A220B11120
4A330C11130
5A440C16180
6A550
7A660
8B670
9B780
10B890
11B9100
12B10110
13B11120
14C11130
15C12140
16C13150
17C14160
18C15170
19C16180
ASAP Download
Excel 2007
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,497
Messages
5,596,508
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top