If I understand it correctly, you want to find a certain item drop (typed out in D5 for example) on the right, and get its value.
So if you type "Gold ore" in D5, you find the value of Gold ore on the right, which would be 93,900, if you type in "Yew seed", you would get 82,139.
If that is what you want to achieve, there is a much easier way than typing 32 IF statements, and it's the
VLOOKUP function (vertical lookup).
If you just want the solution, the formula for you will be at the end of my post, but I'll try to explain what it does first.
Basically you specify a range of cells, VLOOKUP takes the first column of it, finds the first row in which a key you specified (word/number/anything) occurs, then returns the cell found in the nth column of that row, where you specify what n is.
This is what the function you have to put in the cell into which you want to get the returned value looks like:
=VLOOKUP(what_to_look_for, where_to_look_for_it, which_column_it_is_in, is_the_list_sorted)VLOOKUP searches for
what_to_look_for in the first column of the specified
where_to_look_for_it range, and returns the value found in column
which_column_it_is_in of the row in which it found
what_to_look_for.
The
is_the_list_sorted is true if you are using a sorted list, but it will have to be false here.
Here's an example:
We have some data in cells A1 through C3, with some fruits in the first column, their colors in the second one, and some random values in the third one.
We want to get the random values, so we use the =VLOOKUP function as seen in the picture.
what_to_look_for: "apple"
where_to_look_for_it: A1:C3 (and we make the references absolute since those cells are not going to change positions, so $A$1:$C$3, you can just press F4 if you have the cursor in it and Google Sheets will do it for you)
which_columns_it_is_in: 3 (third column has the values we want to get)
is_the_list_sorted: FALSE (because it isn't)
And the result we get is 3, since that's the value in its row.
Of course you don't have to manually type in "apple" or "orange" into the function, you can make it reference another cell, thus making it easily copy-able:
Note that the A6, A7, etc. doesn't have $s, so you can copy them and they will change accordingly.
______________
Now to use it in your sheets (if the name of what you want to look up is in D5, put this function into E5):
=VLOOKUP(D5,$F$2:$I$33, 4, FALSE)Note: you need to type the item drop name the same way it is found on the right, for example if you type "Limpwurt roots", it won't find anything, because on the right it's "Limpwurt root" (without the plural). So you need to be exact for it to work.
If you have any questions feel free to PM me!
