Capture No from a range (Excel 2002)

Topic

New Reply

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

Braddy

Reply | Quote

Replies

For example

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

Reply | Quote

Hi Hans

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

Many Thanks

Braddy

Reply | Quote

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.

Reply | Quote

Hi Hans

Once again my sincere thanks.

Braddy

Reply | Quote

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

Braddy

Reply | Quote

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.

Reply | Quote

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

Braddy

Reply | Quote

How about:
=INDEX(F11:F13,IF(ISNA(MATCH(9.99999999999999E+307,F11:F13)),MATCH(REPT(“z”,255),F11:F13),MATCH(9.99999999999999E+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

Reply | Quote

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.

Reply | Quote

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

Braddy

Reply | Quote

You’re mixing things up. If you go back to the #N/A values, you should use

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

as an array formula (Ctrl+Shift+Enter)

Reply | Quote

Hi Hans

I now works in the cut down version, but when I try to adapt it for the full workbook, I still get the #N/A

Regards

Braddy

Reply | Quote

Try

=INDEX(AA7:AA23,MATCH(FALSE,ISERROR(AA7:AA23),0))

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

Reply | Quote

Hi Hans

Thank you so much for your patience I am eternally gratefull.

Braddy

Reply | Quote

If the single result (or last value) you need is either a text value or a numeric value, here are two options:

[1] A multi-cell set up:

AA24:

=INDEX(AA7:A23,IF(COUNT(AA25:AA26)=2,MAX(AA25:AA26),SUMIF(AA25:AA26,”#N/A”)))

AA25:

=MATCH(9.99999999999999E+307,AA7:AA23)

AA26:

=MATCH(REPT(“z”,255),AA7:AA23)

[2] A single-formula approach:

AA24:

=LOOKUP(2,1/(ISNUMBER(AA7:AA23)+ISTEXT(AA7:AA23)),AA7:AA23)

Reply | Quote

Hi Aladin

Thanks for your input, Just to explain in column AA7:AA23 there will only ever be one entry which will be either numeric or alphanumeric and is then linked to a cell on another sheet,
It is a list of chemicals and only one perfume code is allowed.
having said that I am very interested in your formulas because I have a use for them elsewhere.

Many thanks for your input.

Braddy

Reply | Quote

HI Hans

This is where I am actually trying to place the formula.

Braddy

Reply | Quote

Hi Hans

Sorry I realised you could not see the columns or formula.

Regards

Braddy

Reply | Quote

How about:


=OFFSET(F11,MATCH(TRUE,ISNUMBER(F11:F13))-1,0)


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

Reply | Quote

HI Legare

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

Thanks

Braddy

Reply | Quote

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

Reply | Quote

Hi Legare

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

Braddy

Reply | Quote