Spreadsheet for game items evolution required

**gloom52** · 03-24-2012, 07:57 PM

I have a table as shown below. Under a separate worksheet, I wish to find out the name of next evolution item when I key in the lower tier item's name
eg: A1->A2->A3
B1->B2
if the item that is keyed in is of the highest tier, the formula should put "Item is Ultimate"
Wonder if it has got to do with the VLookup and If functions
Thanks

Name	Tier	Effect
Iron Bangle	A1	HP
Silver Bangle	A2	HP
Tungsten Bangle	A3	HP
Power's Wristband	B1	Strength
Brawler's Wristband	B2	Strength
Black Belt[	D1	Physical Defense
General's Belt	D2	Physical Defense

**DGagnon** · 03-24-2012, 08:34 PM

would you be entering the item ID? (A1) or the name ("Iron Bangle")

it shoudl be easy if you are using the ID, you could have a list of items and use a vlookup mixed with a right string somethign like this

=IFERROR(VLOOKUP(LEFT(A1,1)&RIGHT(A1,1)+1,D:E,2,0),"Item is Ultimate")

**DGagnon** · 03-24-2012, 08:39 PM

i just noticed you are in Excel 2003, you would need to adjust the formula to this:

=IF(ISERROR(VLOOKUP(LEFT(A2,1)&RIGHT(A2,1)+1,D:E,2,0)),"Item is Ultimate",VLOOKUP(LEFT(A2,1)&RIGHT(A2,1)+1,D:E,2,0))

example attached