# LOOKUP A VALUE IN A TABLE AND RETURN VALUE OF FIRST ROW IN THATCOLUMN

omarvn@gmail.com
Guest
Posts: n/a

 3rd Feb 2010
Hi,

In a table as follows,
AC1 AC2 AC3 AC4 AC5
pn1 11 12 13 14 15
pn2 21 22 23 24 25
pn3 31 32 33 34 35
pn4 41 42 43 44 45
pn5 51 52 53 54 55
pn6 61 62 63 64 65
pn7 71 72 73 74 75
pn8 81 82 83 84 85
pn9 91 92 93 94 95
pn10 101 102 103 104 105

I want to lookup for a single value (53, for instance) and to know
which AC it corresponds to (AC3 in this case)

I.e. :
LOOKUPSOMEWAY(53) = AC3
LOOKUPSOMEWAY(74) = AC4
ETC

I have tried LOOKUP function but with no proper results.

Thanks in advance for you help.

Don Guillett
Guest
Posts: n/a

 3rd Feb 2010
Where g1 has the value 53 to find pn5
=LOOKUP(G1,B2:E11,A2:A11)
or
=LOOKUP(G1,B:E,A:A)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:23ec900f-b611-42d9-a1a2-(E-Mail Removed)...
> Hi,
>
> In a table as follows,
> AC1 AC2 AC3 AC4 AC5
> pn1 11 12 13 14 15
> pn2 21 22 23 24 25
> pn3 31 32 33 34 35
> pn4 41 42 43 44 45
> pn5 51 52 53 54 55
> pn6 61 62 63 64 65
> pn7 71 72 73 74 75
> pn8 81 82 83 84 85
> pn9 91 92 93 94 95
> pn10 101 102 103 104 105
>
> I want to lookup for a single value (53, for instance) and to know
> which AC it corresponds to (AC3 in this case)
>
> I.e. :
> LOOKUPSOMEWAY(53) = AC3
> LOOKUPSOMEWAY(74) = AC4
> ETC
>
> I have tried LOOKUP function but with no proper results.
>
> Thanks in advance for you help.
>
>
>

Teethless mama
Guest
Posts: n/a

 3rd Feb 2010
H1 holds criteria

=INDEX(A1:F1,MAX(INDEX((A1:F11=H1)*COLUMN(A1:F11),)))

"(E-Mail Removed)" wrote:

> Hi,
>
> In a table as follows,
> AC1 AC2 AC3 AC4 AC5
> pn1 11 12 13 14 15
> pn2 21 22 23 24 25
> pn3 31 32 33 34 35
> pn4 41 42 43 44 45
> pn5 51 52 53 54 55
> pn6 61 62 63 64 65
> pn7 71 72 73 74 75
> pn8 81 82 83 84 85
> pn9 91 92 93 94 95
> pn10 101 102 103 104 105
>
> I want to lookup for a single value (53, for instance) and to know
> which AC it corresponds to (AC3 in this case)
>
> I.e. :
> LOOKUPSOMEWAY(53) = AC3
> LOOKUPSOMEWAY(74) = AC4
> ETC
>
> I have tried LOOKUP function but with no proper results.
>
> Thanks in advance for you help.
>
>
>
> .
>

lam.ton@zalora.vn
Guest
Posts: n/a

 16th Aug 2012
VÃ*o 21:36:04 UTC+7 Thá»© tÆ°, ngÃ*y 03 thÃ¡ng hai nÄƒm 2010, Teethless mama Ä‘Ã£ viáº¿t:
> H1 holds criteria
>
> =INDEX(A1:F1,MAX(INDEX((A1:F11=H1)*COLUMN(A1:F11),)))
>
>
>
> "(E-Mail Removed)" wrote:
>
> > Hi,
> >
> > In a table as follows,
> > AC1 AC2 AC3 AC4 AC5
> > pn1 11 12 13 14 15
> > pn2 21 22 23 24 25
> > pn3 31 32 33 34 35
> > pn4 41 42 43 44 45
> > pn5 51 52 53 54 55
> > pn6 61 62 63 64 65
> > pn7 71 72 73 74 75
> > pn8 81 82 83 84 85
> > pn9 91 92 93 94 95
> > pn10 101 102 103 104 105
> >
> > I want to lookup for a single value (53, for instance) and to know
> > which AC it corresponds to (AC3 in this case)
> >
> > I.e. :
> > LOOKUPSOMEWAY(53) = AC3
> > LOOKUPSOMEWAY(74) = AC4
> > ETC
> >
> > I have tried LOOKUP function but with no proper results.
> >
> > Thanks in advance for you help.
> >
> >
> >
> > .
> >

your formula works in excel but not in google spreasheet. can you help me to fix it?

THanks

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post hayhur01 Microsoft Excel Programming 0 4th Feb 2010 10:19 AM Eric_G Microsoft Excel Worksheet Functions 2 12th Dec 2009 03:51 AM Matt Microsoft Excel Misc 12 1st May 2009 07:08 AM rlaw68 Microsoft Excel Worksheet Functions 3 24th Jul 2005 04:26 PM Sylvia Microsoft Excel Programming 1 24th Sep 2003 07:34 AM

Features