Frustrating formula issue

gpicone

New Member
Joined
Apr 16, 2009
Messages
5
I have 2 tables of data on seperate sheets. There is repeat data on both sheets but 1 set of data contains a column of information that I need to match to the other but the row order of both sheets is jumbled.

I've tried using the "if" formula, however, I need the true value to return another cell several columns over from the value that matches! Any insight?
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
hi - welcome to the board.

review the help file for vlookup() - post back with more info if you cnt get it sorted
 

gpicone

New Member
Joined
Apr 16, 2009
Messages
5
Thanks for your timely response. I don't think that H or V lookup alone will do it because I can't specify which row the data will be in (it will be in the row that is a match to the lookup value)

Example:

Sheet 1 Sheet 2

A 1 B
B 2 C
C 3 A

From the above, I need to match the 2nd column in Sheet 1 with the corresponding data in sheet 2.

Hope this makes sense.
 

gpicone

New Member
Joined
Apr 16, 2009
Messages
5
In last post the 3rd column is supposed to show under "sheet 2" but it won't show correctly
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

it is the point of vlookup() & similar function that you don't need to specify the matching positions:

with your data in col a & b of sheet1 * col a of sheet 2, the following in b1 of sheet2:

=vlookup(a1,sheet1!a1:b3,2,0)

would return 2 (if I've got your data set up right)
 

gpicone

New Member
Joined
Apr 16, 2009
Messages
5
You're right. It was VLookup. now I have a much easier problem of trying to drag the formula down without moving the Table_array values respectively. Any input on that so that the table_array range is locked in?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
review help file for about cell and range references -

=vlookup(a1,sheet1!$a$1:$b$3,2,0)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top