How do you lookup multiple values in different columns based on variable criteria?

Hi Mitchell,
I'm not too clear on exactly what it is that you want, but my first suggestion would be that if you want to use any of those damage multipliers in further calculations, you should re write them as numerical values rather than use the text values you now have.
Replace the fraction parts with their decimal equivalents, and lose the " x".
You wrote: "a VLOOKUP based on checkboxes determining what damage multiplier I want in a few specific types, the rest being filled in to the standard of 1x "
This is the part that is unclear to me. Can you clarify?
Regards,
Barry

Like (0)


Hey Barry,
Firstly, thank you for taking the time to help me with my little dillemma. Now then...
They're not used in any other calculations, they're only to show the effectiveness of an attack on a particular type. I'd like to search for a typing that has the corresponding resistances (0x, 1/2x and 1/4x) and weaknesses (2x and 4x) that I select.
What I attempted to do was assign a value, 0, 1 or 2 to each of the multipliers, 0 being anything that had no, half or quarter effectiveness, 1 being the standard multiplier (The filler that took over the positions where a positive or negative value wasn't selected) and 2 for anything that had double or quadruple effectiveness. These were then merged into a single cell using the "&" to create a number 17 characters long, all of which consisted of either 1, 0 or 2 which was placed next to it's specific typing combination. The 1st number in the series of numbers would correspond to Fire, the 2nd Water, etc.
I then created a VLOOKUP, the value to search for being determined by a series of checkboxes that would change the standard number (1, seventeen times) for each type. So I'd select a weakness in Water (The second number becoming a 2) and a resistance in Grass (The third number becoming a 0). The VLOOKUP had exact match turned off otherwise I'd have to exactly pick resistances and weaknesses which isn't useful nor what I want. The exact match being turned off had some unexpeted results; Instead of getting as close as it could to all of the individual numbers in the list, it instead found the closest number to it. When this is only in 0, 1 or 2, it can seriously mess up the results. Possibly even away to have the exact match lookup each individual value and get the clostest to each would work.
Thanks for the help again,
Mitchell

Like (0)


Hi Mitchell,
VLOOKUP can be set for accept either an 'exact match' or a 'close match'. Your 17 digit 'number' is actually a 17 character text string (Numbers can handle numbers to a precision of only 15 places). Provided all 17 digits are present, sorting should be the same as for numerical values—the leftmost character is the most significant.
As a text string, your 'number' is sorted/evaluated alphabetically. A 'close match' accepts the 'largest value that is less than or equal to the search value'.
If your search term is 000200000 (a 9 character string), several 'wrong' answers will fit the 'close match' criteria, including all of those listed below:
000200000 (the 'correct' match)
0000xxxxx (x may be any of the three acceptable values)
0001xxxxx (x may be any of the three acceptable values)
The main problem here is that digits in a number (or characters in a text string) have decreasing significance related to their distance from the beginning of the string/number. You want a search in which each character has the same significance as each of the others when compared to the search key. To do that, you need to compare each character in the search string with the character in the same position in the similar string for each type of Pokemon, then take a count of the matches or a sum of the differences.
Here's one approach:
Column A contains labels.
Column B, the 17 digit search term, created in whatever manner you wish, and the similar 17 digit string for each of the characters.
Columns C through S contains a formula that detines, using subtraction, the difference between each digit of the search term and the corresponding digit of each character's profile.
Column C uses SUM() to calculate the total of columns C to S for each row.
T1 uses =MIN(T) to calculate "least different" profile.
Column A is a Header column; Row 1 is a Header row.
Formulas:
C2, and filled right to S2, then down to the last row of data:
=ABS(MID($B$1,COLUMN()2,1)MID($B2,COLUMN()2,1))
T1: =MIN(T)
T2, and filled down column T: =SUM(C2:S2)
The conditional formatting rule set for all body cells in column T is shown below the table.
This may be enough to get you started. Formulas can be tweaked to produce results more closely matching what you're looking for, if necessary.
Regards,
Barry

Like (0)
