Lookup first two letters and return the most recent data

I am trying to read the first 2 letters of a word and return the most recent match from a column in another work sheet where the first 2 letters are the same.

Example:
Cell B1 in the active worksheet contains Bg23

Column F in sheet 4 has many words beginning with Bg---, the most recent is Bg4 and this is what I need to see. #VALUE is the result.

Any help appreciated, formula used -

Formula:

Please Login or Register  to view this content.

Hi,

I'm a bit confused.

For a start, you say that it's column F that contains all these values beginning in "Bg", yet your VLOOKUP is currently set to search in column A (yet return the corresponding value from column 6, i.e. F).

What's more - and the reason you are getting a #VALUE! error - the double-unary (--) is attempting to coerce the string extracted by the LEFT function into a numerical, but clearly "Bg" can never be interpreted as such.

Can you perhaps post a workbook and clarify what it is you are trying to achieve?

Regards

Try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. select the cell where you want to place this formula --> press F2 (function key) --> paste the formula given below in the cell --> and then hold down the Ctrl+Shift and then press Enter.)

Please Login or Register  to view this content.

Is this what you want?

How are you determining "most recent"? Is it done by date, the top of the list, the bottom of the list or some other method?

@sktneer

I strongly recommend not using entire columns as references in array formulas. Unlike e.g. SUMIF(S)/COUNTIF(S), they calculate over the entire range specified, whether beyond the "last used" cell in that range or not.

Regards

Originally Posted by newdoverman

How are you determining "most recent"? Is it done by date, the top of the list, the bottom of the list or some other method?

Good point. I assumed that he is interested in the bottom most value. I may be wrong though.

Originally Posted by XOR LX

@sktneer
I strongly recommend not using entire columns as references in array formulas.
Regards

Thanks for pointing this out. I have edited the formula.

This will find the last entry for "Bg" (the first 2 characters of Sheet1!B1) found on Sheet4 in column F if that is what you are looking for.

Formula:

Please Login or Register  to view this content.

I could see there were responses yesterday but they were not showing up for some reason.
First of all thanks to all for the support and apologies for any confusion.
I attach a spread sheet which will make things clearer.
Sheet 3 shows in red what I am trying to achieve (comes in from Sheet 4 cells A and D 55-64). Sheet 4 is a list which will continue to grow as and when sub assets are changed.
Thanks again

Martin

Try this.......

Array Formula in D2 on sheet3. (Refer to post#3 for how to confirm an array formula)

Please Login or Register  to view this content.

and then drag down.

In C2 on sheet3, try this regular formula (not an array formula)

Please Login or Register  to view this content.

and then drag down.

For detail see the attached sheet.

Genius. Many many thanks sktneer that works perfectly.

You're welcome. Thanks for the feedback.
If that takes care of your question, please mark your thread as solved.
Moreover you may also click on * (star) to Add Reputation.

Good morning sktneer. I set the formula in my spread sheet model which works fine in picking up all of the sub assets from sheet 4 e.g. Bt, Hv, Cmp, etc. If all of the sub asset in dates are the same MAX value then they all show up.

If I then add a sub asset in at a later date, then only that sub asset is visible, the rest of the sub asset cells go blank. Could the formula be modified so that they all stay visible at their relative latest sub asset in date.

I can send a further worksheet if needed.

Thanks again

Martin