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

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

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

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

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

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

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

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