Return last value from row via lookup

Rajoo

New Member
Joined
Mar 2, 2012
Messages
4
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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,
 
Upvote 0
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...
 
Upvote 0
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
ABCDEFGHIJ
1ContractorInvoice1Invoice2Invoice3Invoice4Invoice5Invoice6Invoice7Invoice8Invoice9
2Arnold JonesAA001GH002KJ889993
3Sally GreenA01
4Donna EvansLL90000KJ99999UI888888TY8765DR8735365SD16276ER8716976FT8736763AB173
5James DitzelPOL9387
6Ronald QuimbyHC111HC112HC113
7
8
9
10
11NameLast Invoive Number
12Arnold JonesKJ889993
13Donna EvansAB173
14Ronald QuimbyHC113
Sheet4
Cell Formulas
RangeFormula
B12=LOOKUP(2,1/(INDEX($A2:$X6,MATCH(A12,$A2:$A6,0),0)<>""),INDEX($A2:$X6,MATCH(A12,$A2:$A6,0),0))
 
Upvote 0
try to adapt with this if possible,


Excel 2010
ABCDE
1ContractorsInvoiceContractorLast Invoice
2Contractor1Invoice1Contractor3Invoice4
3Contractor1Invoice2
4Contractor2Invoice1
5Contractor2Invoice2ContractorLast Invoice
6Contractor2Invoice3Contractor2Invoice5
7Contractor3Invoice1
8Contractor3Invoice2
9Contractor1Invoice3
10Contractor3Invoice3
11Contractor2Invoice4
12Contractor1Invoice4
13Contractor1Invoice5
14Contractor3Invoice4
15Contractor1Invoice6
16Contractor2Invoice5
17Contractor1Invoice7
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
 
Upvote 0
try this I assumed skip invoice..try to adapt..


Excel 2010
ABCDEFG
1ContractorsBill1Bill2Bill3Bill4Bill5Bill6
2Contractor1Invoice1Invoice2Invoice3Invoice5Invoice6
3Contractor2Invoice7Invoice9Invoice10Invoice11
4Contractor3Invoice12Invoice13Invoice14Invoice16
5
6
7
8Contractor3Invoice16
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
you can also try this for non-skip invoice..try to adapt if possible..


Excel 2010
ABCDEFG
1ContractorsBill1Bill2Bill3Bill4Bill5Bill6
2Contractor1Invoice1Invoice2Invoice3Invoice4
3Contractor2Invoice7Invoice8Invoice9Invoice10Invoice11
4Contractor3Invoice12Invoice13Invoice14Invoice15Invoice16Invoice17
5
6
7
8Contractor1Invoice4
Sheet1
Cell Formulas
RangeFormula
F8=LOOKUP(TRUE,ISTEXT(INDEX(B2:G4,MATCH(E8,A2:A4,0),0)),INDEX(B2:G4,MATCH(E8,A2:A4,0),0))
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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
Back
Top