Should I use VLOOKUP?
Closed
Allen
-
17 Oct 2011 à 16:55
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 18 Oct 2011 à 23:00
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 18 Oct 2011 à 23:00
Related:
- Should I use VLOOKUP?
- Iserror function in excel with vlookup - Guide
- Transfer data from one excel worksheet to another automatically vlookup - Guide
- How to use vlookup to match data from different sheets - Office Software Forum
- Copying Excel data from one sheet to another based upon contents of a cell - Excel Forum
- Vlookup if two cells match return value from third - Excel Forum
2 responses
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
18 Oct 2011 à 00:05
18 Oct 2011 à 00:05
it is presumed sheet 2 is sorted according to column A and then by column B
first row in both sheets are column headings.
in sheet1 c2 copy this formula
invoke this formula with control shift enter
copy C2 down
the last row is 5 change if the data is larger. wherever 5 comes change it to the number of the last row
first row in both sheets are column headings.
in sheet1 c2 copy this formula
=INDEX(Sheet2!C2:C5,MATCH(A2&B2,Sheet2!A2:$A$5&Sheet2!B2:$B$5,3))+INDEX(Sheet2!C2:C5,MATCH(A2&B2,Sheet2!A3:$A$5&Sheet2!B3:$B$5,3))
invoke this formula with control shift enter
copy C2 down
the last row is 5 change if the data is larger. wherever 5 comes change it to the number of the last row
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
18 Oct 2011 à 23:00
18 Oct 2011 à 23:00
will you be comfortable with a macro
row 1 is having column headings in both the sheets
row 1 is having column headings in both the sheets
Sub test()
Dim r1 As Range, c1 As Range, r2 As Range, filt As Range, tot As Double, x1, x2
With Worksheets("sheet1")
Set r1 = .Range(.Range("A2"), .Range("A2").End(xlDown))
For Each c1 In r1
x1 = c1.Value
x2 = c1.Offset(0, 1).Value
With Worksheets("sheet2")
Set r2 = .UsedRange
'MsgBox r2.Address
r2.AutoFilter field:=1, Criteria1:=x1
r2.SpecialCells(xlCellTypeVisible).AutoFilter field:=.Range("B1").Column, Criteria1:=x2
tot = WorksheetFunction.Sum(r2.Offset(1, 0).Resize(Rows.Count - 1, Columns.Count).SpecialCells(xlCellTypeVisible).Columns("c:c"))
'MsgBox tot
r2.AutoFilter
End With
c1.Offset(0, 2) = tot
Next c1
End With
End Sub
Sub undo()
With Worksheets("sheet1")
Range(.Range("c2"), .Cells(Rows.Count, "C").End(xlUp)).Clear
End With
End Sub
18 Oct 2011 à 18:26
Column A, Column B, Column C
3.1.01, 1.06, 155.38
3.1.01, 1.06, 0.00
3.1.01, 1.06, 0.00
3.1.01, 1.06, 740.83
3.1.01, 1.06, 0.00
3.1.01, 1.06, 0.00
3.1.01, 1.06, 0.00
3.1.01, 1.06, 0.00
3.1.01, 1.08, 0.00
3.1.01, 1.08, 23.53
3.1.01, 1.08, 108.78
3.1.01, 1.08, 0.00
3.1.01, 1.08, 0.00
3.1.01, 1.08, 0.00
3.1.01, 1.08, 0.00
3.1.01, 1.08, 0.00
As you can see there are multiple lines and some of the valves in Column C are 0. Should the formula you provided still work (as I can't seem to get it working)? Thanks again for your help.