vlookup

[darice]

Hi can anyone help me i was wondering if it was possible to use Vlookup to search column A and b for a word and give whatever is 4 columns along back as the answer for eg.
If the word is not in cell A or B i want the answer to be blank.
Hope someone understands what i am on about and can help.

 

[darice]

that works absolutely perfect and exactly what i was after the only one other thing is i want it to search for the word in the column and return the answer so many cells along. so not just search that one cell but two whole columns. i really appreciate your help thanks heaps

 

[T. Valko]

that works absolutely perfect and exactly what i was after the only one other thing is i want it to search for the word in the column and return the answer so many cells along. so not just search that one cell but two whole columns. i really appreciate your help thanks heaps

See if this is what you had in mind:

Book1
	A	B	C	D	E
2	rat	match1	stuff	match10	match16
3	bat	match2	loud	match11	_
4	smat	match3	some	match12	_
5	lat	match4	more	match13	_
6	XXX	match5	big	match14	_
7	trat	match6	bad	match15	_
8	fat	match7	cat	match16	_
9	hat	match8	glad	match17	_
10	gat	match9	mad	match18	_
Sheet1

Formula entered in E2:

=IF(COUNT(MATCH("cat",A:A,0)),INDEX(B:B,MATCH("cat",A:A,0)),IF(COUNT(MATCH("cat",C:C,0)),INDEX(D:D,MATCH("cat",C:C,0)),""))

 

[darice]

Hi this is not quite what I was after I want if in G1 said the word cat it would display the word next to cat or so many cells along in the square that has the formula or if g1 had the word goat it would search for goat in the table and give the result either next to goat or so many cells after.
The answer must be displayed in the cell with the formula if that makes sense hopefully I'm explaining this ok

 

[darice]

Like in the table you posted, the answer would go in E2 but it would look for whatever word is in G2. If G2 said lamb then that's what Cell E2 would search for and return and result for eg. Seven cells along from that answer and display it in E2

 

[T. Valko]

Like in the table you posted, the answer would go in E2 but it would look for whatever word is in G2. If G2 said lamb then that's what Cell E2 would search for and return and result for eg. Seven cells along from that answer and display it in E2

What does "cells along" mean?

 

[darice]

Just ignore that bit if it can just put the word that is right next to it in the same column that would be excellent. So if the formula was in E1 and it asked to search for the word that is in G1 in the table and when it finds the word in the table it returns it with whatever is the word next to it in the row.

So if g1 said frog it would search for frog in the table and put the word next to frog in E1.

I must be so **** at explaining lol

 

[darice]

So your formula was great but I want it to search for whatever word is in G2 not cat but if G2 said cat it would search for cat in the table but if G2 said bird it would search for bird.

 

[T. Valko]

So your formula was great but I want it to search for whatever word is in G2 not cat but if G2 said cat it would search for cat in the table but if G2 said bird it would search for bird.

OK, then all you need to do is replace each instance of "cat" with the cell reference G2:

=IF(COUNT(MATCH(G2,A:A,0)),INDEX(B:B,MATCH(G2,A:A,0)),IF(COUNT(MATCH(G2,C:C,0)),INDEX(D:D,MATCH(G2,C:C,0)),""))

 

[darice]

Thanks so much i will try that when I get to work