excel formula help

montzarella

Hi,
I am trying to get a formula to return one value when you enter another value.

For example, if you enter "Egg" in one cell, I want it to return the value "milk" in the cell next to it, if you enter "spoon" i want it to return the value "fork" in the cell.

Does anyone know a formula that can do this? I used the Match formula for numbers but it wont work with text? Any ideas?

PhoenixParker

vlookup

write a table with egg, spoon etc. in one column and milk, fork etc. in the second column

then it's =vlookup(*box with the word you want to look up*,*co-ordinates of whole table making sure you use $ signs to keep it from changing as you drag the box down*,*column number of the column you want the second cell to have which in this case should be 2*)

Quick Edit - make sure to sort the table you make alphabetically.

fionny

If its all pre-defined just do a vlookup?

montzarella

ok, i'll try the vlookup, i might be back for help though, i find vlookups a bit confusing, thanks

Peppa Pig

You need to use the VLOOKUP function. Difficult to explain in a single post. Try this link - long but you can follow it

fionny

Learning to use Vlookups effectivley is a life skill, well worth knowing them back the front.

montzarella

ok, got the vlookup to work.

another quick question...

the formula will appear in the cells as #N/A until a value is entered, then it populates with the value...is there a way to make the cell be blank and not display the #N/A, even though the formula is there...?

fionny

Use

=iferror(<your vlookup>,"")

This only works in newer versions of excel though.

So

=iferror(vlookup(a1,SHEET1,2,false),"") will return a blank cell if it gets #N/A or any other error. You can modify the last part to be whatever you want.

PhoenixParker

The link provided by peppa pig provides a solution, which I think works in older versions of excel.
It's the iffblank function.

You put an if statement in first that checks if the cell is blank, if it is it returns a blank cell, if it's not it performs the vlookup function.

fionny

2007 Up is fine for my version though and it way less fussy then the ifblank one!

Senecio

fionny wrote: »

Learning to use Vlookups effectivley is a life skill, well worth knowing them back the front.

Not wanting to confuse the OP, but using the compound functions of INDEX and MATCH is worth learning. It provides the ability to return columns to the left of the search column and addresses memory more efficiently meaning faster calculations for large files and smaller overall file sizes.

It's worth looking into for anyone who relies heavily on Vlookups.

http://www.mrexcel.com/articles/excel-vlookup-index-match.php