Answered by:
How do I write this formula???

I have two sheets of data in a workbook. This is what I want to do:
Take value in B2 on sheet 1, find same value on sheet 2, then advance 3 cells to right and bring value in that cell to sheet 1, cell A2 (so that it is next to B2).
Is there a formula for this???
Question
Answers

the formula i gave you works with these values. did you just set up a test worksheet with these values and enter the formula i gave you in a2 and then copy it down to a4?
in a2 on sheet1
=VLOOKUP(B2,Sheet2!A:D,4,FALSE)
results in 2
if your item numbers DO NOT match exactly, you will get #n/a. in your eaxmple, there is no 3344 on sheet2, so it will return #n/a.
 Marked as answer by Sally Tang Tuesday, June 01, 2010 8:03 AM
All replies




i used your original post. you had item numbers in a2:A4 and amounts in b2:b4 on sheet1.
then on sheet2, you have item numbers in a2:a4 on sheet2.
put the formula on sheet2 in b2 and copy it down. if this isn't what you want, then redescribe where the data is and where you want the result.

Gary Keramidas
Excel 2003
"kamikimai" <175479fb5631471e97a7c8eef99b35fb@invalid.com> wrote in message news:f7a14a702661474d98a91f8dbfbe6de8@communitybridge.codeplex.com...
Where do I put this formula? 
Please see second post...first post was incorrect. Thank you!!!
I have two sheets of data in a workbook. This is what I want to do:
Take value in B2 on sheet 1, find same value on sheet 2, then advance 3 cells to right and bring value in that cell to sheet 1, cell A2 (so that it is next to B2).
Is there a formula for this???

try this in a2 on sheet1
=VLOOKUP(B2,Sheet2!B2:E4,4,FALSE)

Gary Keramidas
Excel 2003
"kamikimai" <175479fb5631471e97a7c8eef99b35fb@invalid.com> wrote in message news:99c8dd7a7e5f4f6894a6baf77b25c49e@communitybridge.codeplex.com...
Please see second post...first post was incorrect. Thank you!!!
I have two sheets of data in a workbook. This is what I want to do:
Take value in B2 on sheet 1, find same value on sheet 2, then advance 3 cells to right and bring value in that cell to sheet 1, cell A2 (so that it is next to B2).
Is there a formula for this???



yes it matters, i need the ranges of all of your data.
Well, donâ€™t tell me 3 columns over, give me cell addresses for all of your data. And give an example of both sheets. Iâ€™m guessing what you want and itâ€™s not easy to figure out what you want.
you told me you wanted the value in b2 to be found on sheet2, now you're telling me they're in column A. which is it?
=VLOOKUP(B2,Sheet2!A2:E10721,4,FALSE)
another guess at where your data is and what you want.

Gary Keramidas
Excel 2003
"kamikimai" <175479fb5631471e97a7c8eef99b35fb@invalid.com> wrote in message news:841402a183ef41d0ab115a82e5fc5abf@communitybridge.codeplex.com...
Not sure if this matters, but the values I'm searching for (trying to match) on sheet 2 are in column A, a2:a10721 
Sheet 1 is blank in column A (Price) and Item# in Column B. Sheet 2 has Item #s in Column A and pricing in Column D. I'm trying to take an item # on sheet one (b2), find the corresponding item # on sheet two (somewhere in column a), go over to column d on same row and retrieve corresponding price and bring it to sheet 1, column A, next to item #.

then this should work:
in a2 on sheet1 , enter this formula
=VLOOKUP(B2,Sheet2!A2:D10721,4,FALSE)
and if the range of items on sheet2 will change, use this
in a2 on sheet1
VLOOKUP(B2,Sheet2!A:D,4,FALSE)
copy down

Gary Keramidas
Excel 2003
"kamikimai" <175479fb5631471e97a7c8eef99b35fb@invalid.com> wrote in message news:ae15261023e04a5ebd9ff574763f858e@communitybridge.codeplex.com...
Sheet 1 is blank in column A (Price) and Item# in Column B. Sheet 2 has Item #s in Column A and pricing in Column D. I'm trying to take an item # on sheet one (b2), find the corresponding item # on sheet two (somewhere in column a), go over to column d on same row and retrieve corresponding price and bring it to sheet 1, column A, next to item #. 


just send me a sample workbook.

Gary Keramidas
Excel 2003
"kamikimai" <175479fb5631471e97a7c8eef99b35fb@invalid.com> wrote in message news:7fb422e441624ad1bf3c9853239b371b@communitybridge.codeplex.com...
Tried and still get #N/A...am I clear on what I want? I tried to give as much explanation as possible. Thank you for your patience. 


the formula i gave you works with these values. did you just set up a test worksheet with these values and enter the formula i gave you in a2 and then copy it down to a4?
in a2 on sheet1
=VLOOKUP(B2,Sheet2!A:D,4,FALSE)
results in 2
if your item numbers DO NOT match exactly, you will get #n/a. in your eaxmple, there is no 3344 on sheet2, so it will return #n/a.
 Marked as answer by Sally Tang Tuesday, June 01, 2010 8:03 AM