Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Vlookup with and IF statements
I have two sheets in the same workbook. Master and New.
Masters data will be similar to that of New, EXCEPT, Master will have an additional column of notes, cell H, that is not in sheet New. I need to copy the notes column from sheet Master H to New H But to find the proper row it has to match criteria in 3 cells. Criteria are cells A B and C. So I am thinking, use VLookup for A but how do I also include the AND IFs for B and C. There is no unique field, it has to match 3 fields. Any help is GREATLY appreciated. Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Vlookup with and IF statements
DAve,
Enter this into New, cell H2: =INDEX(Master!H:H,SUMPRODUCT((Master!$A$1:$A$1000= New!A2)*(Master!$B$1:$B$1000=New!B2)*(Master!$C$1: $C$1000=New!C2)*ROW(Master!$A$1:$A$1000))) And then copy down. Adjust the 1000 to be higher than your count of rows on Master... HTH, Bernie MS Excel MVP "DS" wrote in message ... I have two sheets in the same workbook. Master and New. Master's data will be similar to that of New, EXCEPT, Master will have an additional column of notes, cell H, that is not in sheet New. I need to copy the notes column from sheet Master H to New H But to find the proper row it has to match criteria in 3 cells. Criteria are cells A B and C. So I am thinking, use VLookup for A but how do I also include the AND IF's for B and C. There is no unique field, it has to match 3 fields. Any help is GREATLY appreciated. Dave |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Vlookup with and IF statements
Sub addtonew()
With Sheets("Master") MasterLastRow = .Cells(Rows.Count, "H").End(xlUp).Row End With With Sheets("New") NewLastRow = .Cells(Rows.Count, "H").End(xlUp).Row For NewRowCount = 1 To NewLastRow CriteriaA = .Range("A" & NewRowCount) CriteriaB = .Range("B" & NewRowCount) CriteriaC = .Range("C" & NewRowCount) With Sheets("Master") For MasterRowCount = 1 To MasterLastRow If CriteriaA = .Range("A" & MasterRowCount) And _ CriteriaB = .Range("B" & MasterRowCount) And _ CriteriaC = .Range("C" & MasterRowCount) Then .Range("H" & MasterRowCount).Copy _ Destination:=Sheets("New").Range("H" & NewRowCount) End If Next MasterRowCount End With Next NewRowCount End With End Sub "DS" wrote: I have two sheets in the same workbook. Master and New. Masters data will be similar to that of New, EXCEPT, Master will have an additional column of notes, cell H, that is not in sheet New. I need to copy the notes column from sheet Master H to New H But to find the proper row it has to match criteria in 3 cells. Criteria are cells A B and C. So I am thinking, use VLookup for A but how do I also include the AND IFs for B and C. There is no unique field, it has to match 3 fields. Any help is GREATLY appreciated. Dave |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Vlookup with and IF statements
Bernie and Joel,
You two ROCK! 1: I will certainly look at INDEX for future uses, I have never used it before. 2: Bernie, I will use yours ASAP to get this project going. 3: Joel, I will then add yours to it as I am also automating the input of the new data from an outside source and yours will make it a one click deal. I appreciate all of the time everyone gives to this site! Regards, Dave "Joel" wrote: Sub addtonew() With Sheets("Master") MasterLastRow = .Cells(Rows.Count, "H").End(xlUp).Row End With With Sheets("New") NewLastRow = .Cells(Rows.Count, "H").End(xlUp).Row For NewRowCount = 1 To NewLastRow CriteriaA = .Range("A" & NewRowCount) CriteriaB = .Range("B" & NewRowCount) CriteriaC = .Range("C" & NewRowCount) With Sheets("Master") For MasterRowCount = 1 To MasterLastRow If CriteriaA = .Range("A" & MasterRowCount) And _ CriteriaB = .Range("B" & MasterRowCount) And _ CriteriaC = .Range("C" & MasterRowCount) Then .Range("H" & MasterRowCount).Copy _ Destination:=Sheets("New").Range("H" & NewRowCount) End If Next MasterRowCount End With Next NewRowCount End With End Sub "DS" wrote: I have two sheets in the same workbook. Master and New. Masters data will be similar to that of New, EXCEPT, Master will have an additional column of notes, cell H, that is not in sheet New. I need to copy the notes column from sheet Master H to New H But to find the proper row it has to match criteria in 3 cells. Criteria are cells A B and C. So I am thinking, use VLookup for A but how do I also include the AND IFs for B and C. There is no unique field, it has to match 3 fields. Any help is GREATLY appreciated. Dave |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Vlookup with and IF statements
You could add an additional (hidden?) column in Master that is a
concatenation of A, B, and C. Then you could do the VLOOKUP on that column. =CONCATENATE(A1,"|",B1,"|",C1) OR do it in a VBA macro. D-C DS wrote: I have two sheets in the same workbook. Master and New. Master’s data will be similar to that of New, EXCEPT, Master will have an additional column of notes, cell H, that is not in sheet New. I need to copy the notes column from sheet Master H to New H But to find the proper row it has to match criteria in 3 cells. Criteria are cells A B and C. So I am thinking, use VLookup for A but how do I also include the AND IF’s for B and C. There is no unique field, it has to match 3 fields. Any help is GREATLY appreciated. Dave ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with AND, OR IF statements | Excel Worksheet Functions | |||
VLOOKUP and IF Statements | Excel Discussion (Misc queries) | |||
Using Vlookup with IF statements | Excel Programming | |||
Using Vlookup for IF statements | Excel Programming | |||
Help with Vlookup and If Statements | Excel Discussion (Misc queries) |