How do I write this formula???

Question

• 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???

Friday, May 28, 2010 3:04 AM

• 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 Tuesday, June 01, 2010 8:03 AM
Friday, May 28, 2010 4:33 AM

All replies

• don't know where your original post went, but if it's the same. try this:

=VLOOKUP(A2,Sheet1!\$A\$2:\$B\$4,2,FALSE)

change the 4 to the last row of data in column B on sheet1

Friday, May 28, 2010 3:05 AM
• Where do I put this formula?
Friday, May 28, 2010 3:15 AM
Friday, May 28, 2010 3:21 AM
• 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 re-describe where the data is and where you want the result.

--

Gary Keramidas
Excel 2003

"kamikimai" <175479fb-5631-471e-97a7-c8eef99b35fb@invalid.com> wrote in message news:f7a14a70-2661-474d-98a9-1f8dbfbe6de8@communitybridge.codeplex.com...
Where do I put this formula?
Friday, May 28, 2010 3:25 AM
• 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???

Friday, May 28, 2010 3:27 AM
• try this in a2 on sheet1

=VLOOKUP(B2,Sheet2!B2:E4,4,FALSE)

--

Gary Keramidas
Excel 2003

"kamikimai" <175479fb-5631-471e-97a7-c8eef99b35fb@invalid.com> wrote in message news:99c8dd7a-7e5f-4f68-94a6-baf77b25c49e@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???

Friday, May 28, 2010 3:30 AM
• I get #N/A...
Friday, May 28, 2010 3:32 AM
• Not sure if this matters, but the values I'm searching for (trying to match) on sheet 2 are in column A,  a2:a10721
Friday, May 28, 2010 3:37 AM
• 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" <175479fb-5631-471e-97a7-c8eef99b35fb@invalid.com> wrote in message news:841402a1-83ef-41d0-ab11-5a82e5fc5abf@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
Friday, May 28, 2010 3:42 AM
• 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 #.
Friday, May 28, 2010 3:42 AM
• 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" <175479fb-5631-471e-97a7-c8eef99b35fb@invalid.com> wrote in message news:ae152610-23e0-4a5e-bd9f-f574763f858e@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 #.
Friday, May 28, 2010 3:57 AM
• Sheet 1    A            B                             Sheet 2     A                                D

Price        Item#                                       Item #                      Price

(blank)     1122                                         2233                           \$10

(blank)     2233                                         5566                           \$5

(blank)     3344                                          1122                           \$2

I want \$2 in A2, \$10 in A3, etc.

Friday, May 28, 2010 3:57 AM
• 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.
Friday, May 28, 2010 4:01 AM
• just send me a sample workbook.

--

Gary Keramidas
Excel 2003

"kamikimai" <175479fb-5631-471e-97a7-c8eef99b35fb@invalid.com> wrote in message news:7fb422e4-4162-4ad1-bf3c-9853239b371b@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.
Friday, May 28, 2010 4:08 AM

Friday, May 28, 2010 4:09 AM
• Can anyone help me with this???
Friday, May 28, 2010 4:20 AM
• 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 Tuesday, June 01, 2010 8:03 AM
Friday, May 28, 2010 4:33 AM