Help with formula

[Trevor Greene]

Hello,

It has been a while since I posted last, but this community helped me
solve my last problem so I figure I should ask again.


What I have is two sheets in one document. Each containing product
information.

I want to Find the SKU on sheet 2, that is in sheet one, and if they
match, insert the Item number from a different column in there.

Essentially, my two sheets are called Inna and Maria.

In column O on Inna, the first row will have item number 375373. On
sheet Maria, I want to find the cell which contains 375373 in column
i, and then input the value in the same row but column A, which would
return my companies Item number for the product, not the customer.

I am inputting the formula on sheet 3

 

[Don Guillett Excel MVP]

Hello,

It has been a while since I posted last, but this community helped me
solve my last problem so I figure I should ask again.

What I have is two sheets in one document.  Each containing product
information.

I want to Find the SKU on sheet 2, that is in sheet one, and if they
match, insert the Item number from a different column in there.

Essentially, my two sheets are called Inna and Maria.

In column O on Inna, the first row will have item number 375373.  On
sheet Maria, I want to find the cell which contains 375373 in column
i, and then input the value in the same row but column A, which would
return my companies Item number for the product, not the customer.

I am inputting the formula on sheet 3

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[zvkmpw]

What I have is two sheets in one document.  Each containing product

information.

In column O on Inna, the first row will have item number 375373.  On
sheet Maria, I want to find the cell which contains 375373 in column
i, and then input the value in the same row but column A, which would
return my companies Item number for the product, not the customer.

I am inputting the formula on sheet 3

If I understand correctly, this formula might help:
=OFFSET(Maria!$A$1,MATCH(Inna!$O1,Maria!$I:$I,0)-1,0)

It would go in the first row. Assuming all the data are in columns,
copy downward as far as needed.

If a sought item number is missing from "Maria", an #N/A error will
result. If that's unsuitable, wrap the above in an "IF" to get a
result for this situation that's sensible for the application.

 

[Trevor Greene]

For some reason, I may have done something wrong with the formatting,
it did not work properly. I will copy n Paste a brief show of the
sheets to give you a better idea. I got the vlookup function to work
properly, but when it comes down to gathering any other information I
am unable to.
A B C D E F G
H I J K
L M N O
P
Cust# Br # HD Store # ST ZIP Adj # Adj Date Rec Date Qty Unit Price
Adj Amount U/M St rett Item # Ret Sku #
40741071 1 2808 MN 55441 28085828 20100614 20100615 1 $0.89 $0.89
EA 24 A13 F80HD24 118998
40742024 1 3825 OH 43082 38256200 20100614 20100615 1 $11.21 $11.21
EA 12 S58 9014 119313
40740031 1 1940 IL 60102 19401006 20100614 20100615 1 $6.03 $6.03
EA 6 D01 MD11060 344094
40743037 1 2037 IN 46032 20371585 20100614 20100615 1 $15.47 $15.47
EA 4 D01 MD11300 345385
40740018 1 1920 IL 60014 19209563 20100614 20100615 1 $16.86 $16.86
EA 4 D01 MD11500 345476
40741032 1 2740 MI 48312 27406605 20100614 20100615 1 $25.60 $25.60
EA 4 D01 DD11210 345870

^^^ Sheet 1
A B C D E
ITEM COST CURRENT SP CUST. SKU
80006 $1.77 2.09 664008
80007 $2.12 2.32 664008
80008 $2.12 2.32 664008
80010 $4.94 5.65 377898

^^^ Sheet 2

What I did need originally was provided I have two separate excel
documents, then move one of the sheets into the other document, i have
now both files under one workbook. When analyzing the data, lets say
I begin with the use of sheet 1. The number I am most interested in
is the "Ret Sku #", column P. I want to find that number in sheet 2,
by having it search in column E. Column E, and sheet 2 have far more
rows than sheet one. When it finds the match, then I want it to
supply me with the item number from sheet 2, column A, in the same row
it found the match. For simplicity sake, even though the current
example doesn't, let's say sheet 1, column P value is 664008. I want
it to use Column P, row 2 as a search for that value in sheet 2, in
the entire column E. When it does, it see's that the current value
from column P, row 2 of sheet 1 is the same value as column 3, row 2,
sheet 2. Again, because sheet 2 has more rows, I need it to search
the entire column E, not just the coinciding cell from the same row/
column from each sheet. When it finds the match, it will then provide
me the value of 80006 from sheet 2, row 2, column A.


Sheet three currently looks as follows.
A B
ABCSKU Customer SKU
80006 664008
80007 664008
80008 664008
80010 377898
80013 0

^^^ Sheet 3

I have column A, which is the found value between the two sheets, and
column B, which is the value I used for matching, to find column A.

I need to find the SKU from Column B, Row 2, in sheet 1 again in
column P. When it finds that value, I need it to provide me the
information from sheet 1, column J, (QTY), and the value from that
cell in the same matched row.

If you have any idea on the formulas, I'd love some help.

The current formula i used for Column B, sheet 3, because I had to
work somehwat backwards is... =VLOOKUP(A2,Maria!$A$2:$I
$648,4,FALSE) Although, I would love for my formulas to work
the correct way because I only need the information which I am
provided, since sheet 2 is much longer in row count than 1.


Thanks in advance!!!!!

 

[Trevor Greene]

Arg, I just noticed that some of the formatting is messed up. I can e-
mail this to anyone if they can provide me additional help so the
formatting is correct.

 

[Don Guillett Excel MVP]

Arg, I just noticed that some of the formatting is messed up.  I can e-
mail this to anyone if they can provide me additional help so the
formatting is correct.

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Trevor Greene]

If I'm reading your requirement correctly, it's a simple VLOOKUP.

Could you please elaborate then? For the life of me I can't get it to
work.

 

[Rich]

Hello,

It has been a while since I posted last, but this community helped me
solve my last problem so I figure I should ask again.


What I have is two sheets in one document. Each containing product
information.

I want to Find the SKU on sheet 2, that is in sheet one, and if they
match, insert the Item number from a different column in there.

Essentially, my two sheets are called Inna and Maria.

In column O on Inna, the first row will have item number 375373. On
sheet Maria, I want to find the cell which contains 375373 in column
i, and then input the value in the same row but column A, which would
return my companies Item number for the product, not the customer.

I am inputting the formula on sheet 3

If I'm reading your requirement correctly, it's a simple VLOOKUP.