Help with VLOOKUP Formula

[tbruce]

Hi All,

Hoping someone could help me with a VLOOKUP formula. I'm trying to match the value of the cells in column E with the cell values in column A and C and if there is a match, add the values of their corresponding (neighboring columns) counts in column F. Here is a snippet of data. I've been working on the formula and can't seem to get it exactly right. Any help would be much appreciated.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]Log #1[/TD]
[TD]Log 1 Cnt[/TD]
[TD]Log #2 [/TD]
[TD]Log #2 Cnt[/TD]
[TD]Both Logs[/TD]
[TD]Total Cnt[/TD]
[/TR]
[TR]
[TD]12112[/TD]
[TD="align: right"]432[/TD]
[TD]13232[/TD]
[TD="align: right"]323[/TD]
[TD]11111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13232[/TD]
[TD="align: right"]764[/TD]
[TD]12112[/TD]
[TD="align: right"]112[/TD]
[TD]12112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD="align: right"]25[/TD]
[TD]12121[/TD]
[TD="align: right"]376[/TD]
[TD]12121[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12121[/TD]
[TD="align: right"]123[/TD]
[TD]11111[/TD]
[TD="align: right"]324[/TD]
[TD]13232[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

maybe something like...

Excel 2016 (Windows) 32 bit[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Log #1[/TD]
[TD]Log 1 Cnt[/TD]
[TD]Log #2 [/TD]
[TD]Log #2 Cnt[/TD]
[TD]Both Logs[/TD]
[TD]Total Cnt[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]12112[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]13232[/TD]
[TD="align: right"]323[/TD]
[TD="align: right"]11111[/TD]
[TD="align: right"]349[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]13232[/TD]
[TD="align: right"]764[/TD]
[TD="align: right"]12112[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]12112[/TD]
[TD="align: right"]544[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]11111[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]12121[/TD]
[TD="align: right"]376[/TD]
[TD="align: right"]12121[/TD]
[TD="align: right"]499[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]12121[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]11111[/TD]
[TD="align: right"]324[/TD]
[TD="align: right"]13232[/TD]
[TD="align: right"]1087[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=SUMIF($A$2:$C$5,E2,$B$2:$D$5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[tbruce]

Thanks - That worked, but I forgot to mention another value I need to produce and that is the value in Column E, where I need to look at column A and column C, and produce a list of all numbers from those two columns and enter in column E removing any duplicates. I can use the Remove Duplicates and paste, but was hoping for a formula to produce the numbers in column E.

 

[Weazel]

maybe something like...

=IFERROR(INDEX($A$2:$A$5,MATCH(0,INDEX(COUNTIF($E$1:E1,$A$2:$A$5),0),0)),"")