Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Lookup column only
How do i do a reverse lookup to show the heading in the first row. Example:
Say I want to now which month the value 29 show up in the below table? All I want the answer is "MAR" or value of 32 is in "Apr". Many thanks 1 A B C D 2 Jan Feb Mar Apr 3 25 27 29 31 4 26 28 30 32 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Lookup column only
One way to do it. Designate a cell to hold the value to look up, for the
example, I'll use E1. I'll use E2 to put my formula into, although it could be any cell not in the data area. Also assuming that Jan...Apr entries are in row 1. We will build a combined formula that appends the results of individual tests of each column to show which one(s) the value appears in. So in E2, I put this formula: =IF(ISNA(MATCH($E$1,A:A,0)),"",A1) & IF(ISNA(MATCH($E$1,B:B,0)),"",B1) & IF(ISNA(MATCH($E$1,C:C,0)),"",C1) & IF(ISNA(MATCH($E$1,D:D,0)),"",D1) You could extend that on out to include more months tests, of course. If you want to easily see when more than one month contains the value, you could get it to put a space between the month values: =IF(ISNA(MATCH($E$1,A:A,0)),"",A1) & " " & IF(ISNA(MATCH($E$1,B:B,0)),"",B1) & " " & IF(ISNA(MATCH($E$1,C:C,0)),"",C1) & " " & IF(ISNA(MATCH($E$1,D:D,0)),"",D1) "Lisa" wrote: How do i do a reverse lookup to show the heading in the first row. Example: Say I want to now which month the value 29 show up in the below table? All I want the answer is "MAR" or value of 32 is in "Apr". Many thanks 1 A B C D 2 Jan Feb Mar Apr 3 25 27 29 31 4 26 28 30 32 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Lookup column only
Another play to try ..
Assume lookup values are entered in F1 down, eg: 26,31,30,... Put in G1: =IF(F1="","",INDEX(A$1:D$1,,SUMPRODUCT(ISNUMBER(SE ARCH(F1,$A$2:$D$3))*COLUMN($A:$D)))) Copy down. Adapt the range $A$2:$D$3 to suit. The above presumes that lookup values are unique within the lookup range: $A$2:$D$3. If the values are not unique, the expression will return #REF! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lisa" wrote: How do i do a reverse lookup to show the heading in the first row. Example: Say I want to now which month the value 29 show up in the below table? All I want the answer is "MAR" or value of 32 is in "Apr". Many thanks 1 A B C D 2 Jan Feb Mar Apr 3 25 27 29 31 4 26 28 30 32 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Lookup column only
Try this *array* formula:
Enter number to find in E1, then: =INDEX(A1:D1,MAX(IF(A2:D3=E1,COLUMN(A:D)))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Lisa" wrote in message ... How do i do a reverse lookup to show the heading in the first row. Example: Say I want to now which month the value 29 show up in the below table? All I want the answer is "MAR" or value of 32 is in "Apr". Many thanks 1 A B C D 2 Jan Feb Mar Apr 3 25 27 29 31 4 26 28 30 32 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Lookup column only
I was trying to come up with an array formula (not my strong suit at all),
thinking it would be more concise - thanks for putting that up and letting me see it. Nice and tidy. "RagDyer" wrote: Try this *array* formula: Enter number to find in E1, then: =INDEX(A1:D1,MAX(IF(A2:D3=E1,COLUMN(A:D)))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Lisa" wrote in message ... How do i do a reverse lookup to show the heading in the first row. Example: Say I want to now which month the value 29 show up in the below table? All I want the answer is "MAR" or value of 32 is in "Apr". Many thanks 1 A B C D 2 Jan Feb Mar Apr 3 25 27 29 31 4 26 28 30 32 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Lookup column only
Too bad a lot of OPs aren't as gracious as we are.<g
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... I was trying to come up with an array formula (not my strong suit at all), thinking it would be more concise - thanks for putting that up and letting me see it. Nice and tidy. "RagDyer" wrote: Try this *array* formula: Enter number to find in E1, then: =INDEX(A1:D1,MAX(IF(A2:D3=E1,COLUMN(A:D)))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Lisa" wrote in message ... How do i do a reverse lookup to show the heading in the first row. Example: Say I want to now which month the value 29 show up in the below table? All I want the answer is "MAR" or value of 32 is in "Apr". Many thanks 1 A B C D 2 Jan Feb Mar Apr 3 25 27 29 31 4 26 28 30 32 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Lookup column only
I always wondered about that, they get advice for millions of dollars yet a
minority post back with a thank you? Maybe they use the CDO and rate the answers there which obviously nobody with a newsreader can spot? -- Regards, Peo Sjoblom "RagDyeR" wrote in message ... Too bad a lot of OPs aren't as gracious as we are.<g -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... I was trying to come up with an array formula (not my strong suit at all), thinking it would be more concise - thanks for putting that up and letting me see it. Nice and tidy. "RagDyer" wrote: Try this *array* formula: Enter number to find in E1, then: =INDEX(A1:D1,MAX(IF(A2:D3=E1,COLUMN(A:D)))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Lisa" wrote in message ... How do i do a reverse lookup to show the heading in the first row. Example: Say I want to now which month the value 29 show up in the below table? All I want the answer is "MAR" or value of 32 is in "Apr". Many thanks 1 A B C D 2 Jan Feb Mar Apr 3 25 27 29 31 4 26 28 30 32 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Lookup column only
Well, I'm looking at this one in IE and no little green checks on any post.
Oh well, we can just hope that one of us gave a response they could use. They seem to be kind of far and few between all in all. I just looked at my stats: Posts 3087 (and I know I participated in over 1200 separate threads this year) Users that marked some as Helpful: 304 Users that said I came up with 'the' answer: 318 That works out to roughly 1 in 4 on these boards if you assume I was at least helpful in the 1200 I posted in? I know I wasn't always there with the answer, several times I totally misinterpreted the question and gave a totally wrong response. But enough do say Thanks to keep me going. "Peo Sjoblom" wrote: I always wondered about that, they get advice for millions of dollars yet a minority post back with a thank you? Maybe they use the CDO and rate the answers there which obviously nobody with a newsreader can spot? -- Regards, Peo Sjoblom "RagDyeR" wrote in message ... Too bad a lot of OPs aren't as gracious as we are.<g -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... I was trying to come up with an array formula (not my strong suit at all), thinking it would be more concise - thanks for putting that up and letting me see it. Nice and tidy. "RagDyer" wrote: Try this *array* formula: Enter number to find in E1, then: =INDEX(A1:D1,MAX(IF(A2:D3=E1,COLUMN(A:D)))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Lisa" wrote in message ... How do i do a reverse lookup to show the heading in the first row. Example: Say I want to now which month the value 29 show up in the below table? All I want the answer is "MAR" or value of 32 is in "Apr". Many thanks 1 A B C D 2 Jan Feb Mar Apr 3 25 27 29 31 4 26 28 30 32 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reverse Matrix lookup? | Excel Discussion (Misc queries) | |||
Reverse lookup | Excel Worksheet Functions | |||
reverse mapping for LOOKUP table? | Excel Worksheet Functions | |||
How to reverse the column name and row name? | Excel Discussion (Misc queries) | |||
Need help with reverse phone lookup | Excel Worksheet Functions |