Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP A VALUE IN A TABLE AND RETURN VALUE OF FIRST ROW IN THATCOLUMN
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP A VALUE IN A TABLE AND RETURN VALUE OF FIRST ROW IN THAT COLUMN
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 wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP A VALUE IN A TABLE AND RETURN VALUE OF FIRST ROW IN THAT CO
H1 holds criteria
=INDEX(A1:F1,MAX(INDEX((A1:F11=H1)*COLUMN(A1:F11), ))) " 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. . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP A VALUE IN A TABLE AND RETURN VALUE OF FIRST ROW IN THAT CO
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), ))) " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP VALUE OCCURS ONCE IN TABLE ARRAY - RETURN LAST OCCURRENCE | Excel Worksheet Functions | |||
LOOKUP value in a table and return value in first column of same r | Excel Worksheet Functions | |||
Compare value to Lookup table min & max, return contents of 3rd ce | New Users to Excel | |||
how to return an address from a lookup into a table? | Excel Discussion (Misc queries) | |||
need Lookup table to return null or zero | Excel Worksheet Functions |