# Vlookup??

#### Simon4s

##### Board Regular
how do i return these values in the table on the right? (cell G2)

Thanks

 Fork Knife Spoon Plates Name Assets amount Andy G2 Andy Fork 2 Amy Andy Knife 3 Barrie Andy Spoon 6 Bonny Amy Fork 1 Amy Spoon 22 barrie Knife 400 barrie Spoon 20 bonny fork 31 bonny knife 17 bonny plate 8 bonny spoon 9

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try
in G2
=INDEX(\$C\$3:\$E\$12,MATCH(\$F2,C\$3:C\$12,0),MATCH(G\$1,\$D\$3:\$D\$12,0))

copy across and down

UPDATE: Oops! That's not gonna work!

Thinking...

Try SUMPRODUCT.
Copy formula down and across (be careful on how you lock the rows and columns).
Excel Workbook
ABCDEFGHI
1ForkKnifeSpoonPlate
2NameAssetsamountAndy2360
3AndyFork2Amy10220
4AndyKnife3Barrie0400200
5AndySpoon6Bonny311798
6AmyFork1
7AmySpoon22
8barrieKnife400
9barrieSpoon20
10bonnyfork31
11bonnyknife17
12bonnyplate8
13bonnyspoon9
Sheet

Try SUMPRODUCT.
Copy formula down and across (be careful on how you lock the rows and columns).

 A B C D E F G H I 1 Fork Knife Spoon Plate 2 Name Assets amount Andy 2 3 6 0 3 Andy Fork 2 Amy 1 0 22 0 4 Andy Knife 3 Barrie 0 400 20 0 5 Andy Spoon 6 Bonny 31 17 9 8 6 Amy Fork 1 7 Amy Spoon 22 8 barrie Knife 400 9 barrie Spoon 20 10 bonny fork 31 11 bonny knife 17 12 bonny plate 8 13 bonny spoon 9

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:35px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

 Cell Formula F2 =SUMPRODUCT(--(\$A\$3:\$A\$13=\$E2),--(\$B\$3:\$B\$13=F\$1),\$C\$3:\$C\$13)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks! to the both of you!

You're welcome.

SUMPRODUCT in this method is cool. I'll get my head around it one day (all most there.)
I would have gone in SUMIFS family of functions based on what I am comfortable with...
=SUMIFS(\$D\$3:\$D\$13,\$B\$3:\$B\$13,\$F2,\$C\$3:\$C\$13,G\$1)
But I definitely like how the criteria is more clearly defined in the SUMPRODUCT method.

Replies
5
Views
216
Replies
0
Views
591
Replies
4
Views
502
Replies
1
Views
205
Replies
1
Views
157

1,196,260
Messages
6,014,293
Members
441,813
Latest member
elevate

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