Excel Look Up Functions?

Question

My boss is trying to create something that that will review the values in two preceding columns (Column A = Call Center and Column B = Client) against a table on another sheet, and return a cost center from the same table being referenced in Column C. I am racking my brain and using the Help Site but can't seem to find the answer. Can anyone help?

Answer 1

First you have to put the call centers in columns and the clients in rows. At the intersection between a row (a client) and a column (a call center) put the cost.

don't even try to use Vlookup or Hlookup because your comparaison values are located both in columns (call centers) and rows (clients)

Instead use the functions index() and match()
example :
the comparaison table is set like this :
5 call centers in cells B1, C1, D1, E1 and F1
4 clients in cells A2, A3, A4 and A5
Say that the cost for the first client in the first call center is $5, you put 5 in B2 (which is the intersection between column B and row 2)
I call the sheet where the comparaison table is "mytable"

in the column C of your boss'table, enter this formula
=index(mytable!A1:F5,match( cell in B ,mytable!A1:A5,0),match(cell in A,mytable!A1:F1))
cell in B = for each client
cell in A = for each call center

Answer 2

1

Answer 3

Use HLOOKUP and VLOOKUP. Also try Help. Here's a link to these functions:

http://office.microsoft.com/en-us/excel/HP052091631033.aspx?pid=CH062528281033