Multiple Look Ups In Table

[WolfieXL]

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>

Transaction	Electricity	Oil	Uranium
=VLOOKUP($B$1,'Form responses 1'!$B$2:$G18000,6)	=VLOOKUP($B$1,'Form responses 1'!$B2:$G$18000,3)	=VLOOKUP($B$1,'Form responses 1'!$B2:$G$18000,2)	=VLOOKUP($B$1,'Form responses 1'!$B2:$G$18000,4)
Deposit	0	0	8000
Deposit	0	0	8000

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

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.

 

[ncrcnbl]

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)

 

[WolfieXL]

You are correct... Would you be able to give me an example of how to use a sum in this instance?

 

[ncrcnbl]

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.

 

[ncrcnbl]

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.

 

[ncrcnbl]

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.