vLOOKUP help

[jwdemo]

I am wanting to compare 2 sheets and return only the lines that contain matching UPC values to sheet 3.

Sheet 1 is a long list of products, most of which we do not carry in our store. The sheet contains corrections to some products that have recently been made. These corrections are marked in column A with the reason and are highlighted in green. All items that did NOT change have no data in column A.
Sheet 2 is a list of only products we carry.
Sheet 3 I would like to be a list of items that show up on both sheets and that had a change associated with them.

I would like to take the first UPC in Sheet 2 column C (C2) and look in the entire Sheet 1 column H for a match. If there is a match with an item that contains a change, I'd like the entire line copied into sheet 3.

I can use the excel filter tool to show only those with changes but they don't always start on line 1. Would the vLOOKUP be able to only use the visible items on sheet 1?

 

[venkat1926]

if you want to use filter introduce a header at the top (first row) and then filter

 

[jwdemo]

There is a header already and I can filter to show only the changed products. Will vLOOKUP only search through these or will it search through the hidden ones as well?

 

[venkat1926]

some references are there in google search
VLOOKUP VISIBLE DATA
VLOOKUP FILTERED DATA

 

[yesterdays]

I think we can solve this by formula, please post sample of your data

 

[jwdemo]

I wouldn't mind trying to figure it out if I could just get help on entering it into the formula box. I'm just not exactly sure what each entry is needing and I've tried many different attempts with no luck. Could I perhaps get a plain english explanation of the requirements (lookup value, table array, col index number, range lookup)?

 

[t0azt]

	A	B	C	D
1	jon	Sales	alex	Admin
2	charlie	Admin
3	george	Plant
4	alex	Admin
5	frank	Plant
6	luis	Plant
7	jason	Sales

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Heres a quick example:
Lookup value is the value you want to search for, table array is the table you want to find it in, col index is the column number within that table that you want your result to come from, range lookup is either exact (false or 0) or approximate match (true or 1).
so here we want to see what department someone is in (in this case "alex") so we put their name in C1. the vlookup for this would be

=vlookup(C1,A1:B7,2,false)

we want the formula to find the value in C1 in the table A1:B7, return the value from the second column within that table, and false denotes an exact match rather than approximate.
Hope that helps!

 

[jwdemo]

I still can't quite get it with what I'm trying to do. Since I'm not sure how to post a screenshot (and it's on several sheets anyways), I'll just try to explain it differently.

I want to take all of the UPCs in column H of Sheet2 and if they are in column D of Sheet1 I would like those lines put on Sheet3.

 

[exo33]

jwdemo,

Here is my proposal:
1) Create two pivot tables - side by side - on Sheet 3. Source data for first pivot table will be Sheet 1, for second - Sheet 2.
2) Adjust the layout and make sure that the pivot tables do not display items that have blanks in columns where the change is indicated (add this column to filter in pivot table). In other words, make pivot tables display only items with corrections.
3) Then use a combination of INDEX/MATCH to display a list of items that appear on both pivot tables.

You may create a third pivot table - use result of INDEX/MATCH as a source data in this case.

I have a similar solution implemented in one of my workbooks. It's not the prettiest one but works as needed.

You can always add some VBA magic to automate the process.

 

[jwdemo]

I'm just not able to get anything to work it seems.

I just want to get the items that show up on Sheet 1 AND Sheet 2 to be copied to Sheet 3.