# VLOOKUP or INDEX Multiple Columns

#### kiyo052

##### Board Regular
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>

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
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.

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

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

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

Replies
8
Views
763
Replies
3
Views
246
Replies
18
Views
771
Replies
5
Views
1K
Replies
3
Views
197

1,196,329
Messages
6,014,681
Members
441,835
Latest member
rthomas268

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back