Find Lookup?

[ianripping]

I have this problem...


I have a date in A1
I then have a function which works out what the day is in that month in
A2(=TEXT(A1,"DD"))
Then on 12 I have all the dates in that month...

1 2 3 4 5 6 7 8 etc

then in the next row I have a key as H

When the day occurs in A2, I want it to lookup that day in row 12, and
then match that with any rows underneath that row with a H. Then return
that row number.

I have attactched the example.

Very wordy I know!

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=470413

 

[Andy B]

Hi

I can't see your example, but would suggest something like this:

=HLOOKUP(A2,G11:I12,2)
which looks at the value in A2 and finds it in your list of numbers and
returns the value from the cell below it. As you have used the TEXT
function, you might need to use this version for it to work:

=HLOOKUP(VALUE(A2),G11:I12,2)

Andy.

 

[ianripping]

Thanks for the try andy but just get error

 

[Frank Kabel]

Hi
hope I got it right .
1. You don't need this text conversion. Problem is, your values in row
12 are numbers but A2 is a date.
Try the following to get the row number
=MATCH("H",OFFSET($A$1:$A$100,0,MATCH(DAY(A1),A12:AE12,0)-1),0)

This return the FIRST row number with an H under the specific day

 

[Andy B]

Sorry, Ian, I read your post incorrectly.

Andy.

 

[ianripping]

this is great, is there a way that I can get all the row numbers of an
h's that appear for that instance

 

[Frank Kabel]

Hi
no this gives only the first row number. If you want all row numbers
try the following formula in C1 (entered as array formula with
CTRL+SHIFT+ENTER):
=SMALL(IF(OFFSET($A$13:$A$100,0,MATCH(DAY($A$1),$A$12:$X$12,0)-1)="H",R
OW($A$13:$A$100),1000),ROW())
and copy this down

 

[ianripping]

how do i enter with CTRL+SHIFT+ENTER?
Sorr

 

[Frank Kabel]

Hi
just insert the formula in the formula bar and instead of finishing
this entry only with ENTER hit CTRL+SHIFT+ENTER together

 

[ianripping]

I tried selecting the cell, pasting the formula, then pressin
CTRL+SHIFT+ENTER. Then got an error message

Help!

 

[Frank Kabel]

Hi
pste it in your formula bar. Remove linebreaks (due to your newsreader)
and hit CTRL+SHIFT+ENTER

 

[ianripping]

Tried that, get an error.
I think formula is incorrect, can anyone verify

 

[ianripping]

Ok thanks. Now I have the last line number which has a H, how do I fin
the rest

 

[Frank Kabel]

Hi
what error did you get?. Tested this again with your spreadsheet and it
works. You have to enter this formula in row 1 to get it working

 

[Frank Kabel]

Hi
you have to enter this formula in row 1. copy this formula down and
you'll get all other numbers. If you like, send me an email
(frank[dot]kabel[at]freenet[dot]de) and I'll send you your example
sheet with this formula included