[Solved] Adding values with MATCH() and VLOOKUP()? Or Trash_

[robpennington84]

I have the list below in a spreadsheet, in columns A and B. Entries in Column A are entered manually. Values in Column B are calculated automatically, but all have an initial value of 16. Each time the entry is listed in Column A, its value in Column B is increased by 2.

• Strength 18
  Dexterity 18
  Constitution 18
  Intelligence 18
  Wisdom 18
  Charisma 18
  Strength 20
  Intelligence 20
  Intelligence 22
  Constitution 20
  Wisdom 20
  Charisma 20
  Dexterity 20
  Constitution 22
  Intelligence 24
  Wisdom 22
  Charisma 22
  Strength 22
  Intelligence 26
  <BLANK ENTRY> <BLANK VALUE>
  <BLANK ENTRY> <BLANK VALUE>
  ...
  <BLANK ENTRY> <BLANK VALUE>

What would be the best way to automate the counting of the values in Column B? I'm at a loss. Can't figure it out using MATCH or VLOOKUP. If I were programming this, I would just use a loop and some conditional statements, but I'm stumped trying to do this in Calc... HELP!

-Rob

[Dave]

You might need a lot of intelligence and wisdom, but perhaps you mean something like COUNTIF() ?

Or are you looking to reflect the last value for each? A more difficult problem.

David.

[robpennington84]

Hmmm. COUNTIF() looks like it may help, but probably not by itself. Right now, I have each cell in Column A formated to be a drop down menu with all 6 possible entries (Strength,Dexterity,etc...) and want to be able to keep adding 2 to the value associated with each entry, each time it is added to the list.

I can post the sheet as an example in a bit...

[robpennington84]

Okay, here is the example spreadsheet. So all the attributes start at 16 (before they are put into the list). When the user clicks on a cell in Column A (the next empty cell, that is), a drop down menu displays the 6 attributes. When an attribute is chosen (added to the list), it adds 2 to whatever the attribute's last value was. So, if it was the first time that attribute was added to the list, its value would be 18. The second time it was added to the list (however far down the list), that most recent value of 18 should be increased to 20. And so on... Any ideas?

[ken johnson]

=IF(A2="";"";16+COUNTIF(A$2:A2;A2)*2)
in B2 filled down to as far as you like.

Ken Johnson

[robpennington84]

Thanks Ken, that formula does work. But, unfortunately, I've changed up my requirements a little bit. Sorry! I'm not sure if I can use COUNTIF() any more, but if somebody finds a way, then I humbly stand corrected.

I've changed the initial attributes from 16 to a user-chosen value ranging from 8 to 18. Also, instead of incrementing by 2 every time, I've introduced Column C, which contains the quantity to increase the listed attribute by.

In the new example spreadsheet, I have edited the cell contents of the first instance of each attribute in the list to be the initial value of that attribute (derived from Column E) plus the value in Column C. The problems begin when attributes enter the list 3 or more times. The formula =(VLOOKUP($A5;$A$2:$B4;2;0)+$C5) works the first time it is used (which would be the second time the attribute is listed). But, that same formula doesn't work the third time the attribute is listed. I have added borders around the cells that have incorrectly calculated values.

VLOOKUP() seems to return the value for the FIRST instance in the list. Is there a way to do an inverse VLOOKUP? What I mean is: a normal VLOOKUP starts from the top of its range and works its way down. Is there a way to make VLOOKUP start from the BOTTOM of its range and work its way UP? Or, is there another function that does something similar?

-Rob

[ken johnson]

Hi Rob,
Will this work for you?
I've added two helper columns in columns F and G to set up a LOOKUP table.
The formula in column F...
=IF(A2="";"";A2&REPT("a";COUNTIF(A$2:A2;A2)))
either returns an empty string or a concatenation of column A and a string of "a"s.
The number of "a"s is equal to the number of times that the column A value has already appeared up to and including the current row.
The formula in column G...
=IF(A2="";"";B2)
just returns either an empty string or the appropriate column B value.
The formula in column B...
=IF(A2="";"";$C2+IF(COUNTIF(A$2:A2;A2)=1;VLOOKUP($A2;$D$2:$E$7;2;0);VLOOKUP(A2&REPT("a";COUNTIF(A$2:A2;A2)-1);$F$2:$G$101;2;0)))
returns either an empty string or the column C value plus, if the column A value has not appeared before then your lookup table is used else the new lookup table is used. In the new lookup table, its first column is searched for the column A value concatenated with one less "a" (notice the bold "-1" in the formula).
I've filled the formulas in columns B, F and G down to row 572, the same depth as you validity drop down.
And of course you can always hide columns F and G.

Ken Johnson

Counting_Attributes-2.ods

(43.03 KiB) Downloaded 209 times

[robpennington84]

Well dang, that is creative I wouldn't have thought to do it that way, but indeed it works. And less cluttered than the way I worked around it. In my most recent attempt, I think I have solved my problem, but I had to add 6 new columns, as opposed to your 2 new columns. [See the new attachment if you would like] My 6 new columns just kind of keep track of each value and all times. Then I just display the correct entry from the huge new table to the correct cell in Column B.

Thanks! I now have two different ways of accomplishing this. I wish there was another option in the VLOOKUP() function that deals with duplicate entries. Like, you could tell it you wanted the value for the nth instance of your search criteria in the range of cells. That would be sweet. But, alas, I will just have to put my helper columns further over to the side of the spreadsheet so I just can't see them

-Rob

[ken johnson]

I wish there was another option in the VLOOKUP() function that deals with duplicate entries. Like, you could tell it you wanted the value for the nth instance of your search criteria in the range of cells. That would be sweet.

Hi Rob,
Here's an example of looking up a value corresponding to the nth occurrence.
The lookup value is selected from the A2 drop down.
The occurrence is selected from the B2 drop down.
The formula in C2...
=OFFSET($E$1;MATCH(A2&B2;$F$2:$F$20;0);0)
returns the column E value corresponding to the B2 occurrence of the A2 value in column D.
Helper 1 column with formula...
=D2&COUNTIF($D$2:D2;D2)
concatenates the current column D value with the count of that value from D2 down to the current row.
(I've changed from concatenating with a string of "a"s to simply the current count)

So, the MATCH function in the C2 formula looks down column F for the A2 value concatenated with the B2 value. For example if A2 = "Charisma" and B2 = 2 then MATCH(A2&B2;$F$2:$F$20;0) looks for "Charisma2" in $F$2:$F$20 and returns 9.
The OFFSET function in the C2 formula then starts in $E$1 and returns the value in the cell that is 9 rows down (E10), which is 16.

Helper 2 column with formula...
=IF(ROW(A1)>COUNTIF(D$2:D$20;$A$2);"";ROW(A1))
produces a list from 1 up to the count of the A2 value in column D followed by empty strings.
The Data Validity applied to B2 is the cell range defined by the formula...
OFFSET($Sheet1.$G$2;0;0;SUMPRODUCT($Sheet1.$G$2:$G$65536<>"");1)
which is all the cells in column G below G1 that are not blank.

LookUp Occurrence.ods

(22.36 KiB) Downloaded 224 times

Ken Johnson