VLOOKUP or INDEX Multiple Columns

[kiyo052]

I was wondering if there was a formula that is similar to vlookup but throughout a large table.

For example,

My table range would be from A1:H6

If A8= Kate then B8= 4/1/12
If A9= Ashley then B9 = 4/1/15
If A10= Mindi then B10 = 6/1/15
If A11= Andre then B11= 1/1/10

Please let me know if this would be at all possible. Thank you in advance for the help!!!!

	A	B	C	D	E	F	G	H	I	J	K
1	Dave	12/1/12	Richard	1/1/10	Matt	1/1/13	Andre	1/1/10
2	Mark	11/1/12	Erin	6/1/13	Kerry	9/1/14	Todd	10/1/12
3	Justin	1/1/12	Brendan	1/1/14	Jessica	9/1/14	Troy	4/1/13
4	Kate	4/1/12	Ashley	4/1/15	Mandy	12/1/14	Greg	2/1/13
5					Mindi	6/1/15	Tom	12/1/14
6
7
8	Kate	4/1/12
9	Ashley	4/1/15
10	Mindi	6/1/15
11	Andre	1/1/10
12
13

<tbody>
</tbody>

​

 

[jasonb75]

Try

=SUMPRODUCT(($A$1:$G$5=A8)*$B$1:$H$5)

Note that this will not work if there are any duplicate names in the table or if the results to be returned are not numeric (dates are numeric). The formula might show the results as serial numbers instead of dates, in which case you just need to apply the correct format to those cells.

 

[kiyo052]

I'm pulling an error. It is saying #Value.

 

[jasonb75]

Oops, my bad. Try this one instead.

=SUMPRODUCT(--($A$1:$G$5=A8),$B$1:$H$5)

If that causes the same problem then we will need to look at alternatives.