Multiple Look Ups In Table

WolfieXL

New Member
Joined
Jan 4, 2019
Messages
9
Hello Everyone,

I am trying to create a spreadsheet for a game I am playing. Essentially it will add up multiple resources from different players. Essentially the player details are entered in a spreadsheet which contains all the mixed data from all the users. I was then going to create a balance sheet for each player. I would then carry that balance over to an overview sheet. My problem is getting my look up value working.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD="align: center"]Transaction[/TD]
[TD="align: center"]Electricity[/TD]
[TD="align: center"]Oil[/TD]
[TD="align: center"]Uranium[/TD]
[/TR]
[TR]
[TD]=VLOOKUP($B$1,'Form responses 1'!$B$2:$G18000,6)[/TD]
[TD="align: right"]=VLOOKUP($B$1,'Form responses 1'!$B2:$G$18000,3)[/TD]
[TD="align: right"]=VLOOKUP($B$1,'Form responses 1'!$B2:$G$18000,2)[/TD]
[TD="align: right"]=VLOOKUP($B$1,'Form responses 1'!$B2:$G$18000,4)[/TD]
[/TR]
[TR]
[TD]Deposit[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8000[/TD]
[/TR]
[TR]
[TD]Deposit[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8000
[/TD]
[/TR]
</tbody>[/TABLE]

My problem is the formula works but only for the first line not beyond that.

I deally I would like the formula to look for the username in B1, then search the 'Form responses 1' sheet and give me the relevant information. Once that entry has been entered then move onto the next entry in the list for that user.

I hope this makes sense.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Each place where you have that formula, you are looking for whatever is in $B$1 on the sheet you show here, against the Form Responses sheet, whatever matches in column B, lining up with (6, 3, 2, or 4 columns across). Usually you have to add a comma, and FALSE, to get an exact answer, instead of an estimate. Each place you put that formula, you get one answer from the 'Form Responses sheet, It should be returning the first match. But you want a sheet for each player with all of the values from that 'Form Responses sheet for each player? Maybe a sum would be better, for each player? I think it would be easier to try SUMIF.
=SUMIF('Form Responses'!$B$2:$B$18000,$B$1,'Form Responses'!$D$2:$D$18000)
 
Last edited:
Upvote 0
There may be a way to return an array with vlookup, did the SUMIF work?
EDIT: the array formula is not vlookup, still looking at that.
 
Last edited:
Upvote 0
There may be a way to return an array with vlookup, did the SUMIF work?
EDIT: the array formula is not vlookup, still looking at that.

The page here showed how to show multiple rows from a table -
https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/

But this seemed extremely difficult for the application. If you do want to look at it, that's one way to do it. Personally, the Excel Array formulas take me multiple attempts to get right, and each time it feels like I have to start from scratch. This method seems to require multiple layers of those attempts.

creating an Advanced Filter would show multiple rows by player, but you have to recreate the advanced filter each time you would want to update with new data.

With SumIf, you can change the player name criteria in one cell, and by changing that parameter, you can switch your view of the data from player to player.

To make table of players / resources that automatically updates / is extendable
Oil / Electricity / Uranium
Johnny / =SUMIF('Form Responses'!$B$1:$B$100,$A2,'Form Responses'!C$1:C$100) /
Mary /
Michael

It helps to change the column headers in the report to the same order as the column headers in the data table, that way the dollar signs in that formula are set so the formula can copy down as well as across within that report table.
 
Last edited:
Upvote 0
Did you already do a filter on the data? You may want to make a copy of your data file, then, select the table, hit data / filter, an autofilter would show what you want. A video may be the best way to learn to use autofilter if you have not used that before.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top