Return last value from row via lookup

[Rajoo]

Hi

I have an excel sheet with the list of contractors in on column and their list if invoice numbers in the corresponding rows. On another sheet I have a dropdown list with the list of contractors from sheet1. I want to use this dropdown list as a reference to lookup that contractor on sheet1 and return the last used invoice number in that contractor's row. Can anyone help.

Thanks
Rajoo

 

[Firefly2012]

Hi

Something like:

=LOOKUP2,1/(INDEX(Contractors!$A1:$IV1000,MATCH(A2,Contractors!$A1:$A10000,0),0)<>""),INDEX(Contractors!$A1:$IV1000,MATCH(A2,Contractors!$A1:$A10000,0),0))

Assumes that A2 holds the contractor of interest and the contractor names are referenced in column A of the contractors hsheet,

 

[Rajoo]

Tnx but I can't seem to get it to work, I get "#N/A". The contractor names is in column (A) and invoice numbers range (B2:X100) are on sheet1. On sheet2 (A1) is the dropdown list and (B3) is the cell for the result or invoice number used. The invoice number is alpha-numeric.

Tnx once again...

 

[Firefly2012]

So it should look like this (all on one sheet, but you should get the idea) - formula in B12 is copied down for each name:

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Contractor	Invoice1	Invoice2	Invoice3	Invoice4	Invoice5	Invoice6	Invoice7	Invoice8	Invoice9
2	Arnold Jones	AA001	GH002	KJ889993
3	Sally Green	A01
4	Donna Evans	LL90000	KJ99999	UI888888	TY8765	DR8735365	SD16276	ER8716976	FT8736763	AB173
5	James Ditzel	POL9387
6	Ronald Quimby	HC111	HC112	HC113
7
8
9
10
11	Name	Last Invoive Number
12	Arnold Jones	KJ889993
13	Donna Evans	AB173
14	Ronald Quimby	HC113
Sheet4

Cell Formulas
Range	Formula
B12	=LOOKUP(2,1/(INDEX($A2:$X6,MATCH(A12,$A2:$A6,0),0)<>""),INDEX($A2:$X6,MATCH(A12,$A2:$A6,0),0))

 

[MrVillareal]

try to adapt with this if possible,

Excel 2010
	A	B	C	D	E
1	Contractors	Invoice		Contractor	Last Invoice
2	Contractor1	Invoice1		Contractor3	Invoice4
3	Contractor1	Invoice2
4	Contractor2	Invoice1
5	Contractor2	Invoice2		Contractor	Last Invoice
6	Contractor2	Invoice3		Contractor2	Invoice5
7	Contractor3	Invoice1
8	Contractor3	Invoice2
9	Contractor1	Invoice3
10	Contractor3	Invoice3
11	Contractor2	Invoice4
12	Contractor1	Invoice4
13	Contractor1	Invoice5
14	Contractor3	Invoice4
15	Contractor1	Invoice6
16	Contractor2	Invoice5
17	Contractor1	Invoice7
Sheet1

Cell Formulas
Range	Formula
E2	=INDEX(B2:B17,AGGREGATE(14,6,(ROW(A2:A17)-ROW(A2)+1)/(A2:A17=D2),1))
E6	{=INDEX(B2:B17,LARGE(IF(A2:A17=D6,ROW(A2:A17)-ROW(A2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bosco_yip]

Using Firefly2012's table, B12 formula copy down :

1] If your invoice number is text, use this :

=LOOKUP("zzzz",INDEX($A$2:$X$6,MATCH(A12,A$2:A$6,0),0))

2] If your invoice number is number, use this :

=LOOKUP(9.99E+307,INDEX($A$2:$X$6,MATCH(A12,A$2:A$6,0),0))

Regards

 

[MrVillareal]

try this I assumed skip invoice..try to adapt..

Excel 2010
	A	B	C	D	E	F	G
1	Contractors	Bill1	Bill2	Bill3	Bill4	Bill5	Bill6
2	Contractor1	Invoice1	Invoice2	Invoice3		Invoice5	Invoice6
3	Contractor2	Invoice7		Invoice9	Invoice10	Invoice11
4	Contractor3	Invoice12	Invoice13	Invoice14		Invoice16
5
6
7
8					Contractor3	Invoice16
Sheet1

Cell Formulas
Range	Formula
F8	{=INDEX(INDEX(B2:G4,MATCH(E8,A2:A4,0),0),LARGE(IF(ISTEXT(INDEX(B2:G4,MATCH(E8,A2:A4,0),0)),COLUMN(B1:G1)-COLUMN(B1)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MrVillareal]

you can also try this for non-skip invoice..try to adapt if possible..

Excel 2010
	A	B	C	D	E	F	G
1	Contractors	Bill1	Bill2	Bill3	Bill4	Bill5	Bill6
2	Contractor1	Invoice1	Invoice2	Invoice3	Invoice4
3	Contractor2	Invoice7	Invoice8	Invoice9	Invoice10	Invoice11
4	Contractor3	Invoice12	Invoice13	Invoice14	Invoice15	Invoice16	Invoice17
5
6
7
8					Contractor1	Invoice4
Sheet1

Cell Formulas
Range	Formula
F8	=LOOKUP(TRUE,ISTEXT(INDEX(B2:G4,MATCH(E8,A2:A4,0),0)),INDEX(B2:G4,MATCH(E8,A2:A4,0),0))

 

[Rajoo]

Wow, tnx guys. Wonderful help there. Tnx once again.

Rajoo