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.

 

[Aladin Akyurek]

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.

Let K2 house a look up value of interest...

If you are on 2007 or later:

=IFERROR(INDEX($D$2:$D$200,LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MATCH(K2,$B$2:$B$200,0),MATCH(K2,$A$2:$A$200,0)))),"")

Otherwise:

=IF(COUNTIF($A$2:$B$200,K2),INDEX($D$2:$D$200,LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MATCH(K2,$B$2:$B$200,0),MATCH(K2,$A$2:$A$200,0)))),"")

 

[darice]

thanks for your help im trying that now but i am really new to excel and dont really understand that post. What cell should that formula be placed in or does it not matter.
Im not sure if VLOOKUP is the right function i should be using.
I will try to explain what i need in the easiest way possible.

I want if Bills name was to show up in column A1 i want column G1 to say whatever is three columns onwards and if Bills name was to show up in column B1 then it would say whatever is three columns onwards to that.
so IF Bills name was in A1 it would show up whatever is in C1 the third column and if in B1 it would show up what is in celL D1.

I hope i have explained this ok.
I really need this explained to me in the most simplest way possible as im still learning.

Hope you can help.

 

[darice]

if Bills name was in the column it would show whats in the third cell along but if Bills name was not there i want it to not show anything

 

[Aladin Akyurek]

if Bills name was in the column it would show whats in the third cell along but if Bills name was not there i want it to not show anything

Try to post a small sample along with the desired result(s) and the version of Excel you are on.

 

[darice]

im on excel 2003 and here is my example. lol im not very good at explaining it lol


A B C D
1 Cat Dog Frog Elephant
2 Lamb Crab Chimp Mice
3 bee chicken tiger dolphin


ok so i want in cell G1 to display Frog because cat is in cell A1 but if cat is not in cell A1 i want G1 to be blank. But i also want if cat is in B1 it would display Elephant in G1.

Do i make sense now lol what i want to do is alot more complicated its to do with attendance records but if i knew this formula i could finish what i am doing

 

[darice]

so i want it to search for cat in any row and use the answer 4 or 5 etc. cells along in that row as the answer. omg im not sure if im making sense im sorry

 

[darice]

=vlookup(a4,ai4:bd18,14)


this formula works for me on my sheet but i want it to search in cell bd18 as well and give me the result 14 columns along

 

[T. Valko]

im on excel 2003 and here is my example. lol im not very good at explaining it lol


A B C D
1 Cat Dog Frog Elephant
2 Lamb Crab Chimp Mice
3 bee chicken tiger dolphin


ok so i want in cell G1 to display Frog because cat is in cell A1 but if cat is not in cell A1 i want G1 to be blank. But i also want if cat is in B1 it would display Elephant in G1.

Do i make sense now lol what i want to do is alot more complicated its to do with attendance records but if i knew this formula i could finish what i am doing

Maybe this formula entered in G1:

=IF(A1="cat",C1,IF(B1="cat",D1,""))

 

[darice]

I think that is definitely gonna work. Thanks heaps ill get back to you later when I get to a computer to try it