Skip navigation

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

468 Views 3 Replies Latest reply: Feb 4, 2013 1:54 AM by Barry RSS
MitchellWoods Calculating status...
Currently Being Moderated
Feb 3, 2013 8:51 AM

Essentially, I'd like to be able to do a Vlookup but instead of searching for one value only, search for multiple values in separate columns. A smaller version of my current spreadsheet as an example...

 

Attack Type ->
FireWaterGrass
Fire1/2x2x1/2x
Water1/2x1/2x2x
Grass2x1/2x1/2x
Fire/Water1/4x1x1x
Fire/Grass1x1x1/4x
Grass/Water1x1/4x1x

 

The headers are the attack types and the list of types to the left are the receiving Pokemon. Fire does half damage (1/2x) to fire types, Water does double damage (2x) to fire types, etc. I'd like to be able to search for specific damages for each type. For example, I'd like to find a Typing that recieves half (1/2x) damage from Fire-type attacks but also recieves double (2x) from Grass-type attacks. I do want more than just two search criteria though seeing as the actual table is much, much larger.

 

I've tried assigning number values to each damage multiplier and then merging all of them together for a specific typing and doing 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 but the result isn't correct most of the time.

Numbers, Mac OS X (10.7.4)
  • Barry Level 7 Level 7 (29,095 points)

    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

  • Barry Level 7 Level 7 (29,095 points)

    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:

    Picture 6.png.

    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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.