vlookup

[Phill W]

Can anyone help me set up vlookup, I want to type in different words into one cell to make certain words appear in other cell :

type in A1 - help A2 = thanks
type in A1 - please A2 = sorry
type in A1 - only A2 = me

any help would be much appreciated

 

[T. Valko]

Can anyone help me set up vlookup, I want to type in different words into one cell to make certain words appear in other cell :

type in A1 - help A2 = thanks
type in A1 - please A2 = sorry
type in A1 - only A2 = me

any help would be much appreciated

See if this helps:

http://contextures.com/xlFunctions02.html

 

[Phill W]

hi thanks for that, im not having much look setting this formular, is there anything you can do to help me please

 

[kidwispa]

type the words you wish to type in to cell A1 into 1 column (for this example Column C starting in cell C1) and the words you wish to return in A2 in the next column (Column D starting in cell D2)

In cell A2, put the following formula

=VLOOKUP(A1,C1:D3,2,0)

This will give you the answer you require when you input one of the words into A1.

 

[snoopyhr]

Here are few approaches to solve this:

Excel Workbook
	A	B	C	D	E	F
1	Input	Respond
2	please	Sorry	Method 1		Input	Respond
3		sorry	Method 2		help	thanks
4		sorry	Method 3		please	sorry
5		sorry	Method 4		only	me
Sheet2

#VALUE!

Excel Workbook
	E	F
2	Input	Respond
3	help	thanks
4	please	sorry
5	only	me
Sheet2

 

[T. Valko]

hi thanks for that, im not having much look setting this formular, is there anything you can do to help me please

Create a 2 column table with the keywords in the left column and the word to be returned in the right column:

Book1
	F	G
1	Help	Thanks
2	Please	Sorry
3	Only	Me
4	This	That
Sheet2

Then...

A1 = some keyword like Help

Enter this formula in B1:

=VLOOKUP(A1,F1:G4,2,0)

 

[Phill W]

thank you, thats brill, my problem now is if i type in a word not in the colunm N/A appears, can i stop this ?

 

[kidwispa]

thank you, thats brill, my problem now is if i type in a word not in the colunm N/A appears, can i stop this ?

If you are using excel 2007 or later, use IFERROR..

Using the formula given by T.Valko as an example...

=IFERROR(VLOOKUP(A1,F1:G4,2,0),"")

This will mean the cell is left blank if there is an error...

For excel 2003 or earlier...

=IF(ISERROR(VLOOKUP(A1,F1:G4,2,0),"",VLOOKUP(A1,F1:G4,2,0))

 

[T. Valko]

thank you, thats brill, my problem now is if i type in a word not in the colunm N/A appears, can i stop this ?

What version of Excel are you using?

What result do you want to replace the error?

This will work in Excel 2007 and later and will leave the cell blank:

=IFERROR(VLOOKUP(A1,F1:G4,2,0),"")

This will work in any version of Excel:

=IF(ISNA(MATCH(A1,F1:F4,0)),"",VLOOKUP(A1,F1:G4,2,0))

 

[Phill W]

thank you all, been a big help