PDA

View Full Version : [SOLVED] no idea



blue19
03-13-2017, 02:00 AM
I've tried Google to find the formula, but since I don't know what to call what I want to do, I haven't found a solution. I've tried index,match and vlookup, these could work, but again lost in translation. My son is trying to list and value his hockey cards. In sets there are common, semistar, and unlisted stars. There are many of these cards compared to the cards that are listed of more value. He can list the value of the cards worth more, but has to go back and forth to figure out which card is semi, unlisted, or common, and that takes a long while. What he needs (best I can do) He has a list of semistar and unlisted, along with their values, but the names of the players are ALL different. What he needs is a formula to search for the same value (name) to the list of either semi or unlisted (name) and return one of 3 values (common, semistar, unlisted) to a column of cells. Common would be the default value, and semi and unlisted would be different. So COLUMN A would be names, COLUMN D would be the values, and let's say COLUMN P (1-100) would be semistar and COLUMN S (1-100) would be unlisted, and the rest would default to common. So D1 looks through P1:100 for the same name as A1, then through S1:100. If it finds it in P, it returns a set value from a different cell (G3), if S another value from another different cell (G2), if neither a set value from (G1). (A1) Joe Sakic, (D1)?, P1:100 (no Joe Sakic), S1:100 (Joe Sakic) =G3 (Unlisted value) or (A1) Joe Sakic, (D1)?, P1:100 (Joe Sakic), S1:100 (no Joe Sakic) =G2 (Semistar value), or (A1) Joe Sakic, (D1)?, P1:100 (not there), S1:100 (not there), so G1 (Common value)=$0.40. I hope you can make sense of that because I have tried many formulas only to get to many arguments to list the formula. Any help is appreciated.

mdmackillop
03-13-2017, 02:30 AM
Post a workbook (http://www.vbaexpress.com/forum/faq.php?s=&do=search&q=attachment&titleandtext=1&match=all) showing your data and also the expected outcome. Take time to add clarifying explanations etc. I can't promise a solution but without such, wouldn't know where to start.

blue19
03-13-2017, 12:43 PM
I hope this helps. So COLUMN B1 looks in column G And I for the same information as A1, If found in column G2:G6, it returns as E2, if found in column I2:I6, it returns as E3, if not found in either column it returns E1. Thank you again for helping.
Also how do you copy a formula and only have the intended cell change.
A1 =if (D1:D10,E1,if (D1:D10,E2,if (D1:D10,E3,0)))
copy this to next cell without results changing. So,
A2 =if (D1:D10,E1,if (D1:D10,E2,if (D1:D10,E3,0)))
When I try it changes everything tho the next cell down
A3 =if (D2:D11,E2,if (D2:D11,E3,if (D2:D11,E4,0)))
Much appreciated.

mdmackillop
03-13-2017, 12:45 PM
Please Post a workbook (http://www.vbaexpress.com/forum/faq.php?s=&do=search&q=attachment&titleandtext=1&match=all) as this link, not a picture

blue19
03-13-2017, 03:38 PM
Sorry, misunderstood. Here is his workbook. In column L there are 3 formulas I tried as a test and 1 in column B. Columns D and E aren't relevant in the formula. Just A,B,I,L and O.
B1=I6 if A1 is not in L or O, =I7 if A1 is in L, or =I8 if A1 is in O. Again thanks for the help.

blue19
03-13-2017, 04:24 PM
Sorry, I'm at work and got busy. Here is the workbook I forgot to attach.

mdmackillop
03-14-2017, 05:03 AM
For matching names only - as Sheet 1
=IF(NOT(ISNA(MATCH($A1,L:L,0))),$I$2,IF(NOT(ISNA(MATCH($A1,O:O,0))),$I$3))

For non-matching names using colums D, G-I (some liberties taken) - as sheet 2
=IF(NOT(ISNA(MATCH($A1,L:L,0))),$I$2,IF(NOT(ISNA(MATCH($A1,O:O,0))),$I$3,VL OOKUP(D1,$H$4:$I$6,2,0)))

blue19
03-14-2017, 12:59 PM
Thank you very much again. I'll take a poke at your workbook when I get home from work.

blue19
03-14-2017, 09:03 PM
I got some time to look at it, and it does what my son wanted. I tried to input the formula in my workbook, but it gave back "too many functions" error. Maybe because I tried it on my tablet, to send my son. I did change your formula a little because I wanted L and O to decide if the player was a common or not. So I removed the vlookup, and then inserted $I$1. Thank you, I appreciate how fast this was solved. Could not have done it without your help. Final formula I used was =IF(NOT(ISNA(MATCH($A1,L:L,0))),$I$2,IF(NOT(ISNA(MATCH($A1,O:O,0))),$I$3,$I $1))