INDEX AND MATCH? My First Post

[Barnzee]

Hello Everybody, Im ben and Im Stuck!

x X x x x x x x x x x x x x
x |
x |
X v
x
x

(data table)

I need a formula that can look up a certain value that appears down the side, also find a value that appears across the top, and then display the value that appears at their intersection (as demonstrated in my crude diagram)

Essentially, in a seperateb tab, the user will type in employee name, the criteria pertaining to the employee in question they are interested in, and the department he works for. these 3 cells concatenate together to form a value in another hidden cell, which matches the xs that are running down the side(also in a hidden column) - A unique identifier for each criteria, for each employee essentially.

they then type the date they want the information about said employee to pertain to, in the form of a week cell, a month cell and a year cell - again, these three concatenate to a code in a seperate cell that corresponds to the values running across the top.

I need the date concatenated values and the Employee concatenated values to both be involved in a look up, such that they match up and display the value that falls between both criteria.

I have tried MATCH and INDEX but cannot seem to get it to work (V represents the desired value)

Any help would be appreciated as it is for a client.

kind Regards,

Ben.

 

[VoG]

This should help you http://j-walk.com/ss/excel/usertips/tip020.htm

 

[Jonmo1]

Welcome to the board...

Here's a very basic example of a Matrix Lookup..

Hope it helps...

Excel Workbook
	A	B	C	D	E	F
1		A	B	C	D	E
2	1	210	637	214	754	386
3	2	491	960	551	568	307
4	3	988	437	788	71	419
5	4	806	170	536	558	328
6	5	203	283	313	305	446
7	6	515	573	320	960	701
8
9
10	4	C		536
Sheet1

 

[Barnzee]

That was incredibly fast, and i believe it is exactly what i need. You are the man! this board is decent man, ill be coming here often!

Thanks both of you!

 

[T. Valko]

Hello Everybody, Im ben and Im Stuck!

x X x x x x x x x x x x x x
x |
x |
X v
x
x

(data table)

I need a formula that can look up a certain value that appears down the side, also find a value that appears across the top, and then display the value that appears at their intersection (as demonstrated in my crude diagram)

Essentially, in a seperateb tab, the user will type in employee name, the criteria pertaining to the employee in question they are interested in, and the department he works for. these 3 cells concatenate together to form a value in another hidden cell, which matches the xs that are running down the side(also in a hidden column) - A unique identifier for each criteria, for each employee essentially.

they then type the date they want the information about said employee to pertain to, in the form of a week cell, a month cell and a year cell - again, these three concatenate to a code in a seperate cell that corresponds to the values running across the top.

I need the date concatenated values and the Employee concatenated values to both be involved in a look up, such that they match up and display the value that falls between both criteria.

I have tried MATCH and INDEX but cannot seem to get it to work (V represents the desired value)

Any help would be appreciated as it is for a client.

kind Regards,

Ben.

Here's one way...

Book1
	A	B	C	D
1	_	First	Second	Third
2	This	33	13	63
3	That	46	43	41
4	Other	63	47	87
Sheet1

We want to lookup "That" and "Second"...

Lookup values:

• A7 = That
• B7 = Second

=VLOOKUP(A7,A1:D4,MATCH(B7,A1:D1,0),0)

Returns 43