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

Question

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

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 ->	Fire	Water	Grass
Fire	1/2x	2x	1/2x
Water	1/2x	1/2x	2x
Grass	2x	1/2x	1/2x
Fire/Water	1/4x	1x	1x
Fire/Grass	1x	1x	1/4x
Grass/Water	1x	1/4x	1x

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-OTHER, Mac OS X (10.7.4)

Posted on Feb 3, 2013 8:51 AM

Reply

Answer 1

Barry

Level 9

59,861 points

Feb 3, 2013 11:00 PM in response to MitchellWoods

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

Reply

Answer 2

Feb 4, 2013 12:04 AM in response to Barry

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

Reply

Answer 3

Barry

Level 9

59,861 points

Feb 4, 2013 1:54 AM in response to MitchellWoods

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

Reply

Answer 4

thai_anh

Level 1

0 points

Jul 10, 2014 12:21 AM in response to MitchellWoods

Hi Mitchel! I have faced the same problem with yours and could not find the answer in the internet so I have solved it in my way. Would like to share it with you and think it might be useful for others.

I couldn't do the example on your pokemons because haven't sorted out how to look the table to understand how much damage it receives and how much damage it makes. I would show on my example so you could apply it on yours. I nee to build matrix for the vietnamese stock market exchange where there are stocks in the column (under HOSE) and different date in the row (right from HOSE). Three columns from the left show the date, stock and a price. I need to put prices into the matrix so I use VLOOKUP function in addition with IF function to search for two or more values. Don't know why numbers can't search for more than one value. Here is what i wrote for my case

= (IF(VLOOKUP(F1,TRANS_DATE:AD_OPEN_PRICE,2,FALSE)=E3,VLOOKUP(E3,SEC_CODE:AD_OPEN _PRICE,2,FALSE),0)

Reply

Answer 5

SGIII

Level 8

35,288 points

Jul 10, 2014 6:29 PM in response to thai_anh

Hi, since this is an old thread, could you start a new thread and give more specifics on what you want to do?

SG

Reply