In sheet 1, put the following in column B
=VLOOKUP(Sheet1!A2,Sheet2!A:B,2)
The VLOOKUP formula basically says this:
VLOOKUP(X,Y,Z)
"In the field where this formula exists, return what is in the Z numbered column of the range Y, when the value of the first column in Y matches the value in field X."
The source ("Y" in the example, Sheet2 for you) must be sorted by field X (the "Name" field for you) and field X ("Name") must be the first field in Y (Sheet2). VLOOKUP also returns the closest match when the fields are not sorted correctly or it cannot find the value, so it is also a good idea to verify X ("Name") exists in the Y (Sheet2) set before blindly returning the results, such as:
=IF(COUNTIF(Sheet2!A:A,Sheet1!A2) > 0, VLOOKUP(Sheet1!A2,Sheet2!A:B,2),"N/A")
Now you are verifying the value exists before you run the VLOOKUP which will avoid false returns mucking with your Sheet1 data. (Which could cause the wrong person to get paid, and someone else not to be paid at all)
It will also return the first match it finds, so the name values need to be distinct. So an additional check worth doing might be:
=IF(COUNTIF(Sheet2!A:A,Sheet1!A2) > 0,
IF(IF(COUNTIF(Sheet2!A:A,Sheet1!A2) > 1,
"MORE THAN ONE MATCH",
VLOOKUP(Sheet1!A2,Sheet2!A:B,2)
),
"NAME NOT FOUND")
Basically this is called Conditional formatting in Excel. Since you are talking on different thresholds for each item, it will be best to have a threshold column as well so you can change what the threshold will be in the future easily. I could go into great detail on how to do this, however, there is a great place where this is already documented at: https://www.ablebits.com/office-addins-blog/2014/06/05/excel-conditional-formatting-icon-sets-data-bars-color-scales/. This tells you not only on how to do the conditional formatting, but also on what different types of formatting is available to you, and applies to not only Excel 2010, but also from Excel 2007 to currently 2013.
Best Answer
You can do this with an array formula. Do the following:
1,2,3, etc.Under header
1(inA2), type the following.where
Sheet1is the sheet with the original data,A1:A5is the list of names in the original data (including column header), andB1:B5is the list of numbers in the original data (including column header).After typing the formula, place the cursor in the formula bar and press Ctrl+Shift+Enter. This will enter the formula as an array formula. The formula will show up in the formula bar surrounded by curly braces.
Now fill this formula over to all the columns in your new table. Then fill the formula down as far as you need to get all the names to appear.
Explanation of formula:
The
SMALL(IF(Sheet1!$B$1:$B$5=Sheet2!A$1,ROW(Sheet1!$B$1:$B$5)),ROWS(B$2:B2))part of the formula looks through the original data for records that match the number specified by the column header. If a match is found, the row number of each match is returned as this is filled down.The
INDEX(Sheet1!$A$1:$A$5, ...)part of the formula returns the name from the row number found.The
IFERROR(..., "")part checks what is returned byINDEXfor an error. If an error is found, the formula returns nothing.