formula to get data from another sheet excel 2010

[NixLouise]

Hello, I need to write an formula to put a number in column H that will look at the name in column B then look at the same name on a different work sheet in the same book called "SHEET2". Once I have the matching name found on "SHEET2" I need to get the data from column E on "SHEET2" and put it in original sheet "SHEET 1" in column R.

So excel will be filling in column R based on the username and the information about the user on the second sheet.


Not sure if that will make sense to anyone. I think I need to use something like vlookup but am still trying to learn how it works.

Any pointers in the right direction will be really appreciated.

 

[Menumorut]

I'm not sure on what you really want but it seems you need vlookup.
Usually vlookup will look at a reference, search it in an array and retrieve data from one column of your choice if it finds the value you where looking for on that row.
For the formula to work as it is normally the columns you want data from should be to the right of the value you are looking at. This may be bypassed and lots of materials on this are available at google.com

 

[HSharp]

I did something similar with match and offset.

=OFFSET(TableSource[[#Headers],[ID]],MATCH(TableDestination[@ID],TableSource[ID],0),4)

The formula is put in the destination table
This has two tables which both use the same ID for rows of data (most of the other headers are different), it matches the ID of the row of the destination table (hence the [@ID]) with the ID of the row of the Source table and then offsets it by 4 columns to the right (which is the data I want from the source table).

That is using Tables, you could just identify ranges instead of Table[#Headers],[ID]]. This only works though for unique rows which is what I wanted, if you wanted it to get data from a range of data with no unique identifier (i.e you have two of the same username in Sheet2) then it might not work, or you might be able to play with the formula to make it work.

Also if you do use this and try it on different workbooks be aware that the formula will only work on active workbooks so if you close the source workbook and recalculate you will get #REF errors, can bypass this by turning off automatic calculation and then just Find and Replace "=" on ranges you want calculated.