Reverse Lookup column only

[Lisa]

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

 

[JLatham]

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,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,0)),"",D1)

 

[Max]

Another play to try ..

Assume lookup values are entered in F1 down, eg: 26,31,30,...

Put in G1:
=IF(F1="","",INDEX(A$1$1,,SUMPRODUCT(ISNUMBER(SEARCH(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!

 

[RagDyer]

Try this *array* formula:

Enter number to find in E1, then:

=INDEX(A11,MAX(IF(A23=E1,COLUMN(A))))

 

[JLatham]

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]

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 !
-----------------------------------------------------------------------------------------------

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.

 

[Peo Sjoblom]

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

 

[JLatham]

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.