Vlookup from different rows and columns

Tata Kosi

New Member
Joined
Jan 9, 2014
Messages
5
Hi, I want to know if it is possible to vlookup data in different row and column?

Example of data:

Ages</SPAN>
Gender</SPAN>
Color</SPAN>
Origin</SPAN>
Profession</SPAN>
James Bond</SPAN>
28yrs</SPAN>
male</SPAN>
dark</SPAN>
african</SPAN>
actor</SPAN>
Tom Brown</SPAN>
32yrs</SPAN>
male</SPAN>
fair</SPAN>
european</SPAN>
athlete</SPAN>
Kate Blue</SPAN>
18yrs</SPAN>
female</SPAN>
fair</SPAN>
asian</SPAN>
broadcaster</SPAN>

<TBODY>
</TBODY>


And you want to vlookup from another sheet, a particular data (say profession) for the respective names.
To have something like:

Profession</SPAN>
James Bond</SPAN>
Actor</SPAN>
Tom Brown</SPAN>
Athlete</SPAN>
Kate Blue</SPAN>
Broadcaster</SPAN>

<TBODY>
</TBODY>


Thanks
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
As long as the first column in our vlookup range is the same as your lookup value then yes, this is exactly what vlookup is for. You just need to select the range of data including the column you want to pull back and when you choose the column number in the vlookup formula use the correct one. So in your example the column number would be 6.
 
Upvote 0
Thanks Brian. I however need further clarification. In my example, the name and the data I want to pull back are in different rows. The column is indeed 6 as you rightfully stated but the data is not in the same row. It is actually in the row beneath the name. That is to say, for example, the name is in Row1 Column1 and the data I want to pull back is in Row2 Column6.

Thanks.
 
Upvote 0
This is where you would use offset, index and match.

Excel Workbook
ABCDEF
1AgesGenderColorOriginProfession
2James Bond
328yrsmaledarkafricanactor
4Tom Brown
532yrsmalefaireuropeanathlete
6Kate Blue
718yrsfemalefairasianbroadcaster
8
9
10Profession
11James Bondactor
12Tom Brownathlete
13Kate Bluebroadcaster
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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
Back
Top