# Thread: Capture No from a range (Excel 2002)

1. ## Capture No from a range (Excel 2002)

Hi

I am tryi to capture a cell entry from a lookup range, there will only ever be one number in the range.

Please see attached for visual explantion.

Many Thanks

2. ## Re: Capture No from a range (Excel 2002)

For example

=INDEX(F11:F13,MATCH(9.99999999999999E+307,F11:F13 ))

3. ## Re: Capture No from a range (Excel 2002)

Hi Hans

That perfect , could I impose on you to explain the MATCH(9.99999999999999E+307.

Many Thanks

4. ## Re: Capture No from a range (Excel 2002)

<code>
=OFFSET(F11,MATCH(TRUE,ISNUMBER(F11:F13))-1,0)
</code>

That is an array function and must be confirmed with Ctrl/Shift/Enter.

5. ## Re: Capture No from a range (Excel 2002)

MATCH tries to find the largest value that is less than or equal to the search value. Since 9.99999999999999E+307 is the largest number that can be entered in a cell, ANY number will do, so MATCH returns the position of the number in the range.

6. ## Re: Capture No from a range (Excel 2002)

HI Legare

Hans code worked fine, I get #N/A with yours.

Thanks

7. ## Re: Capture No from a range (Excel 2002)

Hi Hans

Once again my sincere thanks.

8. ## Re: Capture No from a range (Excel 2002)

You didn't hold down Ctrl+Shift when you pressed Enter to confirm the formula. Read the end of my previous post.

9. ## Re: Capture No from a range (Excel 2002)

Hi Legare

My sincere apoliges, I don't know how I could have missed that. I must have had a senior moment.

10. ## Re: Capture No from a range (Excel 2002)

Hi Hans

I have had a spanner thrown into the works, I have been requested to add some new codes to lookup list, which are alphanumeric ie K123456,
Can this formula be changed or do I need something totally different?

Many Thanks

11. ## Re: Capture No from a range (Excel 2002)

Try this. It is an array formula, so it should be confirmed with Ctrl+Shif+Enter, not just Enter

=INDEX(F11:F13,MATCH(FALSE,ISERROR(F11:F13)))

Repeat: it is an array formula, so it should be confirmed with Ctrl+Shif+Enter.

12. ## Re: Capture No from a range (Excel 2002)

HI Hans

I entered your formula, but it just returns a blank cell, I need to capture it whether it is numeric or alphanumeric.

Regards

13. ## Re: Capture No from a range (Excel 2002)

=INDEX(F11:F13,IF(ISNA(MATCH(9.99999999999999E+307 ,F11:F13)),MATCH(REPT("z",255),F11:F13),MATCH(9.99 999999999999E+307,F11:F13)))

Note: the cells F11:F13 need to be blank if you want them ignored. F12 and F13 currently have text in them which will be found, unless that is also part of the puzzle that some text string should be "ignored"?

Steve

14. ## Re: Capture No from a range (Excel 2002)

In the spreadsheet you attached to the first post in this thread, the other cells contained #N/A. The formula I proposed assumed that this was still the case. You have now changed the setup, so the assumption is not valid any more. If you restore the #N/A's, the formula will work.

If you prefer to keep the blank cells, use

=INDEX(E11:E13,MAX((F11:F13<>"")*ROW(1:3)))

again, as an array formula, i.e. confirm with Ctrl+Shift+Enter.

15. ## Re: Capture No from a range (Excel 2002)

HI Hans

I have reverted back to the #N/A and enterered yours and legares formula into the attached spreadsheet but can't seem to get them to work.

Regards