vlookup (newbie, STILL)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ken - 30 Dec 2004 02:09 GMT

I have =vlookup(h25,g19:h23,2,false) which returns a number when I type a
name. How can I reverse this? Basically, I am using MIN in a formula in
column B and when I get the result of the MIN formula I would like it to
return the corresponding name from column A. I am using cells B1, B3,
B5..... thru B15 and A1, A3, A5.... thru A15. Column B MIN gives me a number
00.00 and I need the name in column A to be the result of the vlookup.
Incidentally the vlookup I mentioned at the beginning came from this group.
I've not progress enough to do this on my own as of yet. Obviously.
Thanks,
Ken

Reply to this Message

Dave Peterson - 30 Dec 2004 02:18 GMT

=index(g19:g23,match(min(h19:h23),h19:h23,0))

Debra Dalgleish has some nice instructions for =vlookup() and =index/match at:
http://www.contextures.com/xlFunctions02.html
and
http://www.contextures.com/xlFunctions03.html

> I have =vlookup(h25,g19:h23,2,false) which returns a number when I type a
> name. How can I reverse this? Basically, I am using MIN in a formula in
[quoted text clipped - 6 lines]
> Thanks,
> Ken

Signature

Dave Peterson

Reply to this Message

Ken - 30 Dec 2004 02:24 GMT

Thanks Dave. I'll give this a try.
Ken

> =index(g19:g23,match(min(h19:h23),h19:h23,0))
>
[quoted text clipped - 16 lines]
>> Thanks,
>> Ken

Reply to this Message

Ken - 30 Dec 2004 02:27 GMT

Dave,
The 0 at the end of this formula... does it tell the program to look in the
first column for a match? I was thinking the 2 in my original formula was
telling the program to look in the second column. Just wondering.
Thanks,
Ken

> =index(g19:g23,match(min(h19:h23),h19:h23,0))
>
[quoted text clipped - 16 lines]
>> Thanks,
>> Ken

Reply to this Message

Frank Kabel - 30 Dec 2004 07:36 GMT

Hi
it just tells the MATCH function to look for an exact match. See Excel's
helpt for more on this third parameter of the MATCH function

Signature

Regards
Frank Kabel
Frankfurt, Germany

> Dave,
> The 0 at the end of this formula... does it tell the program to look in
[quoted text clipped - 24 lines]
>>> Thanks,
>>> Ken

Reply to this Message

Dave Peterson - 30 Dec 2004 15:11 GMT

Just to add to Frank's reply.

=vlookup() has that return this column parm.

But =match() doesn't.

And if you noticed, each of those ranges consisted of exactly one column:
G19:G23
and
H19:H23

It was just returning the value from the same row in G19:G23 that had the match
in H19:H23.

> Dave,
> The 0 at the end of this formula... does it tell the program to look in the
[quoted text clipped - 27 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Ken - 31 Dec 2004 01:27 GMT

Thank you both for the information. The MATCH formula worked well and it is
greatly appreciated.
Ken

>I have =vlookup(h25,g19:h23,2,false) which returns a number when I type a
>name. How can I reverse this? Basically, I am using MIN in a formula in
[quoted text clipped - 7 lines]
> Thanks,
> Ken

Reply to this Message