Lookup last used cell in a row, using vlookup to identify which row

adamsneale

New Member
Joined
Mar 30, 2013
Messages
1
Good Morning All,

I have a spreadsheet which contains a list of references in column A (e.g. NN/NNN/). every column after this contains incrimentally increasing cells to finish off the reference, making the reference complete as NN/NNN/NNNN.

I would like to be able to enter a value on another sheet which would reference the initial column (NN/NNN/) and return the last entry for the complete reference (NNNN).

So looking at the example below I would like to be able to enter "01/002/" in a cell on the next sheet and have it return "0002"

A B C D E
01/001/ 0001 0002 0003
01/002/ 0001 0002
01/003/ 0001 0002 0003 0004

Any suggestions would be greatly appreciated!!

Many Thanks,
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
What are the expected results for 01/001/, 01/002, and 01/003 supposing that the exhibit you posted is the data sheet?
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
Hi

Assuming that the values in B:E are numerical (just formatted with leading zeros) then try this:


Excel 2010
ABCDE
101/001/000100020003
201/002/00010002
301/003/0001000200030004
4
5
6
7
8
9
10
11
12LookupResult
1301/002/0002
1401/003/0004
Sheet1
Cell Formulas
RangeFormula
B13=LOOKUP(9.9E+307,INDEX($B$1:$E$3,MATCH(A13,$A$1:$A$3,0),0))


If they are actual text values then replace the 9.9E+307 with REPT("z",255)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,565
Messages
5,838,135
Members
430,530
Latest member
sonofagunn123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top