Formula To Look Up Value Based On 2-3 Criterias

[a_mahey]

Hi all,I have two sheets one is called Data and the other is Report (see below tables).
I have three of each reports i.e. 3 for TMT, 3 for hybrids, 3 for autos and so on. They are all WTD, QTD and YTD for each report.
I need to pull the data into the report tab using a formula. Vlookup works for cell B2 and C2 in the report tab but when I am trying to pull in the AT3 QTD (cell E3) from the data tab into cell D2 of the report tab, the vlookup will not work as it will return the first result i.e. lookup value in the vlookup will be 'TMT'.

Given I have a number of rows 70+ because of different reports (column A) I need a dynamic formula please which will pull in QTD A3 (cell E3) value and the YTD AT4 (cell F4) values from the Data tab into the Report tab (cells D2 and E2).
I did try using the match and index together but it did not work due to my limitations.

Thanks

DATA tab
[TABLE="width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Report[/TD]
[TD]Period[/TD]
[TD]AT1[/TD]
[TD]AT2[/TD]
[TD]AT3[/TD]
[TD]AT4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TMT[/TD]
[TD]WTD[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TMT[/TD]
[TD]QTD[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]300[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TMT[/TD]
[TD]YTD[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]500[/TD]
[TD]600[/TD]
[/TR]
</tbody>[/TABLE]

REPORT tab
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Report[/TD]
[TD]WTD AT1[/TD]
[TD]WTD A2[/TD]
[TD]QTD A3[/TD]
[TD]YTD AT4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TMT[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]300[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[a_mahey]

Any ideas guys please?

 

[jmacleary]

Hi there. You could add a new column to the left in the DATA which concatenates columns A and B. Then do your lookup for the concatenated value. So, A2 would have TMTWTD -formula =CONCATENATE(A2,B2) , a3 would have TMTQTD etc. and your formula in the report tabe would lookup TMTQTD into cell D2. something like =VLOOKUP(CONCATENATE(A2,"QTD"),DATA!A:G,5,FALSE)

 

[a_mahey]

Hi there. You could add a new column to the left in the DATA which concatenates columns A and B. Then do your lookup for the concatenated value. So, A2 would have TMTWTD -formula =CONCATENATE(A2,B2) , a3 would have TMTQTD etc. and your formula in the report tabe would lookup TMTQTD into cell D2. something like =VLOOKUP(CONCATENATE(A2,"QTD"),DATA!A:G,5,FALSE)

Hi, the issue is that the Report tab, I can't perform a concatenate there as that has only one line per report and the data needs to be pulled into that tab from WTD, QTD, and YTD into different cols.

 

[RandomStranger1577]

Insert a column in the "DATA" tab after "Period" and concatenate the "Report" and "Period" columns. Then go to the "REPORT" tab and insert the formula below. Just change the column reference based on what data you're trying to pull in. Let me know if this helps.

=VLOOKUP($A$2&LEFT(B$1,3),Data!$C:$G,2,0)

 

[DanteAmor]

Try this

<td style="background-color:#92d050; font-weight:bold; text-align:center; ">Sheet Report</td></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></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:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Type</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">WTD AT1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">WTD AT2</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">QTD AT3</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">YTD AT4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >TMT</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">300</td><td style="text-align:right; ">600</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >hybrids</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">1000</td><td style="text-align:right; ">4500</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=SUMPRODUCT((Data!$A$2:$A$7=$A2)*(Data!$B$2:$B$7=LEFT(B$1,3))*(Data!$C$1:$F$1=RIGHT(B$1,3))*(Data!$C$2:$F$7))</td></tr></table></td></tr></table> <br /><br />


Copy the formula to the right and then down.


<td style="background-color:#92d050; font-weight:bold; text-align:center; ">Sheet Data</td><b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></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:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Type</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Period</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">AT1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">AT2</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">AT3</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">AT4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >TMT</td><td >WTD</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">100</td><td style="text-align:right; ">200</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >TMT</td><td >QTD</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">300</td><td style="text-align:right; ">400</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >TMT</td><td >YTD</td><td style="text-align:right; ">5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">500</td><td style="text-align:right; ">600</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >hybrids</td><td >WTD</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">100</td><td style="text-align:right; ">200</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >hybrids</td><td >QTD</td><td style="text-align:right; ">8</td><td style="text-align:right; ">9</td><td style="text-align:right; ">1000</td><td style="text-align:right; ">2000</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >hybrids</td><td >YTD</td><td style="text-align:right; ">17</td><td style="text-align:right; ">21</td><td style="text-align:right; ">3000</td><td style="text-align:right; ">4500</td></tr></table> <br /><br />

 

[RandomStranger1577]

Just to clarify, you're using the LEFT formula to pull out just the first 3 characters of your headers in the REPORT tab, so you can do the concatenate that way. You'll need to change cell you're referencing based on which one you want. Should work fine if you just start in the first cell (i.e. B2) and copy it from there. But just wanted to clarify

 

[a_mahey]

Just to clarify, you're using the LEFT formula to pull out just the first 3 characters of your headers in the REPORT tab, so you can do the concatenate that way. You'll need to change cell you're referencing based on which one you want. Should work fine if you just start in the first cell (i.e. B2) and copy it from there. But just wanted to clarify

Can't use the concatenate with left as the report names are not limited to three characters, they can range from 3-20. So not applicable sorry

 

[DanteAmor]

B1 = WTD AT1

You can divide this into two rows:
B1= WTD
B2 = AT1

Or divide the data with a hyphen
B1 = WTD-AT1

Or explain how to identify each data, if the 2 data you have in a single cell.
It is recommended that you structure your data in the report to help carry out searches. Otherwise yourself will be causing headaches for not having a clear and orderly information.