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

[adamsneale]

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,

 

[Aladin Akyurek]

What are the expected results for 01/001/, 01/002, and 01/003 supposing that the exhibit you posted is the data sheet?

 

[Firefly2012]

Hi

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

Excel 2010
	A	B	C	D	E
1	01/001/	0001	0002	0003
2	01/002/	0001	0002
3	01/003/	0001	0002	0003	0004
4
5
6
7
8
9
10
11
12	Lookup	Result
13	01/002/	0002
14	01/003/	0004
Sheet1

Cell Formulas
Range	Formula
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)