Is this possible? (Excel)

choyaa

I have a problem with Excel and I am not sure how to solve it. I have two tabs in tab one there is in column A "Staff Ids" and in column F there is a heading called "employee name".


Tab one feeds in from a tab called "tab 2" that is not identical in layout, however I want to use the employee id in tab one and link this to the "employee ID" in tab two and return the corresponding entity under the "employee name" column. Is this possible? The problem with the "employee name" column in tab two is that it can change locations .


Any help would be great.

rmg1

As long as the column you're wanting to get the names from doesn't move you'll be fine.
The formula would be:-
=index(tab1!F:F,match(a2,tab1!A : A,0))

The formula needs to go into Tab2 and makes the following assumptions:-
Your "Employee IDs" in Tab2 start in cell A2 and go down
Column F in Tab1 will always hold the employee names and never move
Column A in Tab1 will always hold the Staff IDs and never move

You could use a VLOOKUP for this but I prefer INDEX/MATCH.

choyaa

Thanks, I've tried what you have recommended but it was not been a success. I cannot put formula into tab 2.


Lets just say for example, employee ID's in tab 1 are in column A and there is a blank column (B) where I want to return employee names from tab 2.


In tab 2 the IDs are in column A but the employee names may vary in location.


What can I do?


Within tab

Nick_C

If you want to store data in Excel and be able to manipulate or analyse itt, you should structure it properly. The employee names in tab 2 should always be in the same column.

victor2

If tab2 has column names in the first row and each time you update the spreadsheet, the names are always in the named column, then in tab1 where you want the name to appear:
=VLOOKUP(A2,'tab2'!A:K,MATCH("NAME",'tab2'!$1:$1))

This would be on row 2 and assumes the name column has a title of NAME, and it will always be between columns A and K, but you can adjust that, and there's no need to touch tab2 - but it MUST have column names if you don't know which column the name will be in.

Edit.
The data in tab2 must also be in order by ID for this to work.

Heedtheadvice

Lookups and index/match formulas work particularly well with data that may move if those cells holding the data (I.e. the database of the source data to be searched)is in cell ranges that have range names rather than cell references.

Consider the case where you for example insert new or delete columns, then the formulas with cell references will automatically change their references (say IDs are in col B, names in col D and an extra column is inserted between B and C) the formula should automatically look for names in col E. (Provided relative referencing is used rather than absolute cell row/column cell reference with $ symbol)

However if the tab 2 structure is not changed after the formulae are set (such as the columnare not added or deleted) but the name data is just entered in different columns, then the formula needs to be changed!

Range name method:
If the range of the employees names is given a range name then the formula using the rangename (instead of cell references) need not change even if the data if moved (as per column inserting above) or just in a different position, provided that position had an identical range name.
That latter method is far more flexible and Excel can be set up to have a macro to find the column header content "employee" and set the column to be the range name used.

Similarly the column for IDs can be automatically located and range named.

Range names provide extra benefits as, provided sensible names are selected, the formulas are much more self explanatory looking more like written language rather than more obscure cell nomenclature!

But, the question alluded to by an earlier post, why would you wish to change the data location? Far better if possible to have a fixed structure to make life easier. Care should also be exercised if the lookup (IDs in your case) have duplicates (intentionally or otherwise) or are not sorted on order as that will affect which method you use in the lookup to search for the ID and then the corresponding employee name.

choyaa

Thanks all for your help, I got this formula to work but it was a variation of the above and to be honest I fluked it a little. Below is what I used, however can anyone explain the exact theory behind this code?


=VLOOKUP(A30,'tab 2'!A:IV,MATCH(tab1!F29,'tab2'!1:1,0),0)

victor2

choyaa wrote: »

Thanks all for your help, I got this formula to work but it was a variation of the above and to be honest I fluked it a little. Below is what I used, however can anyone explain the exact theory behind this code?


=VLOOKUP(A30,'tab 2'!A:IV,MATCH(tab1!F29,'tab2'!1:1,0),0)

The MATCH function returns the number of the column in the first row of tab2 which contains the exact same value (or contents) as cell F29 in tab1. So if F29 in tab1 contains "employee name" AND cell E1 in tab2 also contains "employee name", the match statement will return the value 5, as column E is the 5th column. The "0" at the end of the match statement dictates that the contents must match exactly.
Then the VLOOKUP statement (which is presumably in row 30 in your example) looks up the value in cell A30 (from the same sheet as the formula is in, presumably tab1) in the whole of tab2, where the first column must contain the value being looked up (Employee ID?) in alphabetical/numerical order. It returns the value in the corresponding 5th column, as defined by the MATCH statement. The zero at the end of the VLOOKUP says that it must be an exact match.

Note that if you copy that statement, the F29 in the MATCH function will change as it is a relative value, not absolute. It is not clear why you are using tab1!F29 as the cell containing the field name, when the VLOOKUP refers to A30, which you would expect to be a relative value. I suspect, as it is, your formula will not work if copied to another cell.

choyaa

Thanks, I 'll just go through it one final time to make sure I have understood it.


Look at the value in cell A30 of tab 1.
Go to "tab 2" and search for this value throughout the entire spreadsheet.
Once the value is found match it with the heading in tab one within the field F29.


The next bit of 1:1,0),0) confuses me.

bod1467

MATCH(tab1!F29,'tab2'!1:1,0) ... is a single expression - check the Excel docs for it. 'tab2'!1:1 is what defines the whole worksheet of tab2.

The MATCH expression is then one of the fields of the VLOOKUP expression - check the Excel docs for it.