Conditional Functions
Posted by charles blake on January 14, 2002 2:44 PM
I'm using excel to list accounting data for payroll entries by individual employee. Each employee has a number of entries in each payroll. My accounting data list is according to employee number. I would like to add the corresponding name next to the number. I also have an employee name list that runs according to employee number. I've frustrated myself trying to find a way use an "IF" function to get the name next to the employee number. My idea was to us this sort of logic: IF, on Sheet1, cell B1 matches a number in the range B1:B700 on Sheet2, print the corresponding name from Sheet2 (adjacent cell), in cell C1 on Sheet1. In other words, I'd like to create a formula that searches for matching data in a range on another sheet and then copies data from a corresponding cell in parallel range on that sheet. I hope my explanation isn't too confusing.
Posted by Qroozn on January 14, 2002 3:00 PM
can you use a lookup?
if they are in order then you can set it to llokup the corresponding column entry
Posted by Russell Hauf on January 14, 2002 3:51 PM
Fortunately, there's already a formula that does what you ask.
Ok, let's say that on sheet2 you have emp. #s in column A and employee names in column B. You can use the VLOOKUP function. In your sheet1, type a formula like the following:
=VLOOKUP(B1, sheet2!$B$1:$C$700, 2, FALSE)
...where B1 contains the cell of the employee number on SHEET1, B1C700 is your range with the employee numbers and names on sheet2 (B1 to B700 has the numbers, and C1 to C700 has the corresponding names). What this function does is to search down column B until it finds a match, then it returns the value in the column number you specify in the 3rd argument (in our case 2). The FALSE we specified tells Excel to find an exact match. If no match is found, the function will return #N/A.
Hope this helps,
Russell
