IF formula not working properly

[Kazman]

Excel 2010

This formula below assumes column A is filled with names and column C is filled with phone numbers. Cell E7 is a drop-down list from column A.

If I select a name from the drop-down (ie. "john" is in Cell A1 and "john" is chosen in E7), the formula does not return the phone #. However, (found this out by accident) that if I move the formula to the same row (ie. A3-A500), the formula DOES work.

Why won't it work if it's in a different row or on a different sheet?!?!?

=IF(A:A=E7,C:C,"see it's not working")

The end result I'm looking for is to have Sheet1 contain the names & numbers and Sheet2 to have a drop-down list based on column A in Sheet1. When I choose a name from that drop-down, it should return the corresponding phone # from Sheet1 Column B.\

Thanks in advance!

 

[rs2k]

Do a search on VLOOKUP

HTH
Colin

 

[MrRajKumar]

Try VLOOKUP,

=VLOOKUP(E7,Sheet1!A:C,2,FALSE)

 

[Kazman]

I'm not very familiar with vlookup and don't quite understand the syntax. I will eventually want to show more info than just one corresponding column.... it would look something like this:

A B C D E F
name phone street zip info1 info2

i'm trying to create a sheet2 that when i select a name from the dropdown it will display the info from each of the corresponding cells matching the name. How would i do this using vlookup? or without vlookup?

 

[MrRajKumar]

Let's say on Sheet2 A2 has a drop down list of Names, In B2 enter this formula & copy across.

=VLOOKUP($A2,Sheet1!$A:$F,COLUMNS($B2:B2)+1,FALSE)

See the below link about VLOOKUP

http://www.contextures.com/xlfunctions02.html

 

[Kazman]

MrRajKumar,

Thank you very much. That is exactly what I needed. I also found that if I use +2, +3, etc... i can get it to show the other columns also.

If it's not too much trouble, would you mind explaining to me in english what =VLOOKUP($A2,Sheet1!$A:$F,COLUMNS($B2:B2)+1,FALSE) is saying?

i know it's vertical lookup and compare the value in A2 of the current sheet to the data located in Sheet1 columns A-F. But that's where i'm lost. What tells it to look in column A for the name information? and why in the COLUMNS() code does it reference column B when column A is where i want my info from?

 

[rs2k]

Extract from a previous post.

If you are not familliar with VLOOKUP, a quick breakdown is thus; =VLOOKUP(A44,'Project Tasks'!$L$5:$M$29,2,0)

=VLOOKUP(A44,'Project Tasks'!$L$5:$M$29,2,0) A44 is the cell that contains the info you want to look for.
=VLOOKUP(A44,'Project Tasks'!$L$5:$M$29,2,0) 'Project Tasks'!$L$5:$M$29 this is the worksheet and range to search in.
=VLOOKUP(A44,'Project Tasks'!$L$5:$M$29,2,0) 2 this is how many columns (to the right) to look in.
=VLOOKUP(A44,'Project Tasks'!$L$5:$M$29,2,0) 0t his means return nothing if a match is not found (sometimes FALSE is used, its the same thing)

The $ sign tells Excel that it is an absolute reference, very useful when ;dragging' formulas across/down so the ranges dont automatically update, theres plenty of info for this on the web.

HTH
Colin

 

[Kazman]

Thank you for breaking it down rs2k. I'm learning as I go. But my spreadsheet is looking good so far.