Hard Formula Question

[Leslie]

This is a tough one for me. I'm going to use the actual cell numbers
in my spreadsheet for the explanation.

In cell C2 I have a name (Tom)

In a column running from A24 through A108 I have many different names.

In a column running from B24 through B108 I have a number next to the
names. For example:

Ed 14.4
Tom 16.9
Mary 23
Steve 11
John 19.4

Here is what I want to happen in cell F18:

I type in a name into cell C2, (Tom) and I want the corresponding
number (16.9) next to the name in my columns to automatically appear
in cell F18.

Is that possible? Hope I explained it clearly.

Thanks,

Leslie

 

[Don Guillett]

If you look in the help index for VLOOKUP your life will become much easier

 

[Leslie]

If you look in the help index for VLOOKUP your life will become much easier

Thanks for pointing me to the proper help file. I read, or tried to
read it three times, but I have no idea what it's saying. They lost
me with the named range lookup, col_index_num, table_array,
lookup_value.

Sometimes I can piece things together from the help files, but this
time it's over my head.

Maybe what I want to do can't be accomplished because the cell (f18)
where I want the answer to appear isn't in the same row as my table of
data.

I don't know.

Thanks anyway,

Leslie

 

[Dave Peterson]

With your table in A24:B108 and your input cell in C2, this is the most basic
version of the formula you'd want:

=vlookup(c2,$a$24:$b$108,2,false)

to stop from displaying errors if C2 is empty:
=if(c2="","",vlookup(c2,$a$24:$b$108,2,false))

And finally, to stop displaying errors if you mistype a name:
=if(c2="","",if(isna(vlookup(c2,$a$24:$b$108,2,false)),"Missing Name",
vlookup(c2,$a$24:$b$108,2,false)))

(That's all in one one cell)

Debra Dalgleish has lots of notes on =vlookup():
http://www.contextures.com/xlFunctions02.html

 

[Stan Brown]

This is a tough one for me. I'm going to use the actual cell numbers
in my spreadsheet for the explanation.

Why would you do anything else?

In a column running from A24 through A108 I have many different names.
In a column running from B24 through B108 I have a number next to the
names. For example:

Ed 14.4
Tom 16.9
Mary 23
Steve 11
John 19.4

Here is what I want to happen in cell F18:

I type in a name into cell C2, (Tom) and I want the corresponding
number (16.9) next to the name in my columns to automatically appear
in cell F18.

This is a straightforward application of VLOOKUP -- by
"straightforward", I mean it's that once you know the name of the
function you need.

Read about it in the help file -- it should be part of your toolbox.

 

[Leslie]

Thank you, Dave. That worked perfectly the first time. It is really
very cool, especially all of the variations. I used the third one
that gives you the "missing name" message if you make a typo.

I guess I didn't comprehend how to include the table range--the
c2,$a$24:$b$108--part. It seems pretty straight forward, now that I
see your example.

Thank you so much again. I apprecitate it very much.

Leslie

 

[Ricky]

Hi Leslie:

In cell F18, enter the following formula:
=Vlookup(C2,A24:B108,2,FALSE)

This is the quick solution to your question as you have asked it. There are other things to consider and if you need help with these scenarios, please let us know...

- It would a good idea to apply a name to the range (A24:B108) and then use that name in the formula.

- Is it possible that you have multiple instances of the same name in the range? Two Tom's, for example.

- You could use cell validation for C2 to insure only names from the list are entered. This would keep you from entering "Saly" when "Sally" is what you intended.



EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com