vlookup to find last data in row

Mega1

New Member
Joined
Sep 19, 2017
Messages
25
I have one sheet with a list of fruit then next 6 colums are marked week1 to 6

Fruit Week1 Week2...
Apple 23 45
oranges 10
pears 5 6 7

on a second sheet I want to lookup the fruit and bring back the last row with data in it

if I lookup apples it will return 45
oranges 10

can anyone help
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Welcome to the board.

Try:

ABCDEFGHIJK
1FruitWeek 1Week 2Week 3Week 4Week 5Week 6pears7
2Apple2345
3Oranges10
4Pears567

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
K1=1/LOOKUP(2,1/INDEX(Sheet1!A2:G20,MATCH(J1,Sheet1!A2:A20,0),0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Change the ranges to match your sheet.
 
Upvote 0
this did not work

sheet1

FilmCompainyDate StartWeek1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15
2495American MadeUNIVERSAL INT'L25/08/20170.50.450.4
2490Annabelle:CreationWARNER BROS INT'L11/08/20170.450.40.30.30.30.3
2483Atomic BlondeUNIVERSAL INT'L09/08/20170.50.450.40.350.3
2479Captain UnderpantsFOX INT'L24/07/20170.25
2474Despicable Me 3UNIVERSAL INT'L30/06/2017
2499DunkirkWARNER BROS INT'L21/07/20170.60.550.50.450.450.450.450.450.45
2463Dunkirk.WARNER BROS INT'L21/07/20170.60.550.50.45
2481Emoji MovieSONY INT'L04/08/20170.450.450.450.40.350.30.250.250.250.25
2484Everything EverythingWARNER BROS INT'L25/08/2017
2496Girls TripUNIVERSAL INT'L26/06/2017
2488Hitmans BodyguardLIONSGATE17/08/2017
2497Nut Job2WARNER BROS INT'L11/08/2017
2498Rough NightSONY INT'L25/08/20170.450.40.30.25
2477Spider Man:HomecomingSONY INT'L07/07/2017
2487The Dark TowerSONY INT'L18/08/2017
2500American AssassinLIONSGATE14/09/20170.50.40.4
2501DetroitEONE FILMS08/08/2017
2502ITWARNER BROS INT'L08/09/2017

<colgroup><col width="64" style="width:48pt"> <col width="157" style="width:118pt" span="2"> <col width="73" style="width:55pt"> <col width="64" style="width:48pt" span="15"> </colgroup><tbody>
</tbody>


sheet2

No.FilmCOMPANYTERMS
2495American Made(3)UNIVERSAL INT'L32#NAME?
2490Annabelle:Creation(5)WARNER BROS INT'L5330%
2474Despicable Me 3(11)UNIVERSAL INT'L1160%
2501Detroit(5)EONE FILMS5180%
2499Dunkirk(8)WARNER BROS INT'L8745%
2481Emoji Movie(6)SONY INT'L6930%
2484Everything Everything(3)WARNER BROS INT'L3100%
2496Girls Trip(11)UNIVERSAL INT'L11110%
2488Hitmans Bodyguard(4)LIONSGATE4120%
2502IT(1)WARNER BROS INT'L1190%
2497Nut Job2(5)WARNER BROS INT'L5130%
2498Rough Night(3)SONY INT'L31430%
2477Spider Man:Homecoming(10)SONY INT'L10150%
2487The Dark Tower(4)SONY INT'L4160%
2500American Assassin(-1)LIONSGATE-11750%

<colgroup><col width="64" style="width:48pt"> <col width="319" style="width:239pt"> <col width="130" style="width:98pt" span="3"> <col width="238" style="width:179pt"> </colgroup><tbody>
</tbody>

in terms I need to lookup the number in colum a and return the last data in sheet1 that match the number in colum a
 
Upvote 0
=1/looup(2,1/INDEX(Film!A:AC,MATCH(A6,Film!A:AC,0),0)) is not returning the inforequired
 
Upvote 0
No.FilmCOMPANYTERMS
2495American Made(3)UNIVERSAL INT'L32#name ?
2490Annabelle:Creation(5)WARNER BROS INT'L5330%
2474Despicable Me 3(11)UNIVERSAL INT'L1160%

<colgroup><col width="64" style="width:48pt"> <col width="319" style="width:239pt"> <col width="130" style="width:98pt" span="3"> <col width="238" style="width:179pt"> </colgroup><tbody>
</tbody>

this is sheet 2 #name ? is what I get the number in column a is what I ma looking up in shee2
 
Upvote 0
I have one sheet with a list of fruit then next 6 colums are marked week1 to 6

Fruit Week1 Week2...
Apple 23 45
oranges 10
pears 5 6 7

on a second sheet I want to lookup the fruit and bring back the last row with data in it

if I lookup apples it will return 45
oranges 10

can anyone help

Let A:Z of Sheet1 house the data.

Let A2 of Sheet2 house Apple, a fruit of interest.

In B2 enter:

=LOOKUP(9.99999999999999E+307,INDEX(Sheet1!A:Z,MATCH($A2,Sheet1!A:A,0),0))
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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