Lookup Functions for a range of cells

Please help! Here's what I'm trying to do:

I have a table in one sheet as follows:
1 Sam 40
2 Jeff 23
3 Mark 23
4 Vince 55
5 Kate 33
6 Susan 67
7 Troy 77
8 Debbie 44

The first column is the Employee Number, the second is the Employee Name & the third is the weekly sales figures.

In a different tab, I want to be able to type in the Employee Number (between 1 & 8) in Cell A1 and I want B1 to automatically populate with the Employee Name & C1 to automatically populate with the Sales Figure.

My understanding is that VLookup, Index/Match & Offset/Match functions can only return the value for one particular cell and not a range of cells. What other functions should I try to accomplish this?

Thanks.

Would filter be good enough for you?

go to first row Data -> Filter...

This how you can filter by any Employer, by any number, or multiple, or sort them ascending, descending etc

Hi PHUAG,

welcome to the forum.

VLOOKUP and INDEX/MATCH can only return one value per cell, but since you want two cells to deliver two different lookups, this is a very simple solution for a very common scenario. If your table as described above is in Sheet1, starting in A2 (with row 1 having the labels for employee number, name, and sales amount, then on any other sheet you can use this:

put the emplyoyee number in A1

B1 =vlookup(A1,Sheet1!$A$2:$C$100,2,false) -- for employee name
C1 =vlookup(A1,Sheet1!$A$2:$C$100,3,false) -- for sales figure

you can copy this formula down and it will apply the lookup for the employee number entered in the current row.

hth

Your syntax is wrong:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value is value you want to look for...
table_array is data you looking at
col_index_num is number of column you want to return

Hang on.... Hang on!

Take a step back. You want to type any employee number from 1 to 8. You put that number into A1. Then A1 = whatever number you put in there. Then B1 = the forumula I posted above, C1 = the formula I posted above. Have you tried it???

You can copy the whole setup down a few rows and enter different employee numbers in A2, A3, A4, etc.

You will only ever enter one employee number into one cell, and that cell will be evaluated by the Vlookup function.

Maybe you want to post an example of your file, so we can have a look and explain some more?
You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

cheers

This is what I have, it doesn't use vlookup, just regular lookup.

B1 and C1 will stay blank until you decide to input a employee number in A1

B1:

Please Login or Register  to view this content.

C1:

Please Login or Register  to view this content.

The range is static, so you can paste down a few more rows for comparison.

Good luck

haru, LOOKUP will always return a match. If no exact match is found, LOOKUP will return the largest value that is less than or equal to the lookup value. Also, LOOKUP requires the lookup range to be sorted.

With VLOOKUP, the range does not have to be sorted and with the last parameter set to FALSE will only return an exact match. In most cases this is what the requirements are. VLOOKUP can also be used with the TRUE parameter, in which case it works just like LOOKUP, also requires a sorted lookup range and will return the next best match. If VLookup cannot find a value with the FALSE parameter set, it will return #N/A, which can be a valuable pointer for the user that the data set is incomplete.

Since it cannot be assumed that employee numbers will be sorted ascending, Lookup is probably not the right choice.
Since, when you enter an employee number, you probably want exactly that employee's data , and not the person's data who has the next highest number, Lookup is definitely not the right choice.

You will need either Vlookup (with FALSE as the last argument) or an INDEX/MATCH combination (with 0 as the last Match argument) that will return an error when the exact employee number is not found in the data, instead of returning the next employee down the list.

If your Payroll department were giving your bonus to someone else, just because your name is missing from the list by some dud mistake, and your silly, lazy colleague gets your bonus, because HR is using LOOKUP instead of VLOOKUP, tell me, would you care?

teylyn,

Thank you very much for clarifying the use of vlookup and lookup. I learned the method of lookup a few years back that taught me most of the excel stuff I know now and never had the chance to learn about vlookup, I just always sorted and lived with it. I think you've just saved me a lot of time, I need to adjust my macro now. Thanks again