# Vlookup with a difference

#### jaypatel

##### Active Member
Hi,
my column headings are dates which run on a weekly basis..... so cell b1 is 4th October, cell c1 is 11th October etc, increasing by 7 days.......

in Column A is the name of the member. so cell a2= person 1; cell a3 = person2 etc

The data ie cells b2....CA20000 (and will be increasing) is full of whole numbers. The data is how many times they logged onto our website.

The problem: is it possible to tell me going through row by row (member by member) when was the last time they logged on? Of course there could be breaks when they have logged on.... for example:

Person1: Week 1= 45; week 2= 90; week 3= 0; week 4=50; week 5=0
Person 2: week 1=2; week 2=0; week 3=0; week 4=0, week 5=0.

So in this example, the last time this person 1 logged in was week 5
And for person 2 would be week 2.

I think its to do with lookup's but it is very complicated, and would appreciate any advice or scripts that can help.

Regards

Jay

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Andrew Poulsom

##### MrExcel MVP
The problem: is it possible to tell me going through row by row (member by member) when was the last time they logged on? Of course there could be breaks when they have logged on.... for example:

Person1: Week 1= 45; week 2= 90; week 3= 0; week 4=50; week 5=0
Person 2: week 1=2; week 2=0; week 3=0; week 4=0, week 5=0.

So in this example, the last time this person 1 logged in was week 5
And for person 2 would be week 2.

Please explain why person 1 is week 5 and person 2 is week 2, when week5=0 in both cases.

#### Domenic

##### MrExcel MVP
Shouldn't Person1 equal Week4, and Person2 equal Week1? If so, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(B1:CA1,MATCH(2,1/(INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0)<>0)))

Hope this helps!

#### barry houdini

##### MrExcel MVP
In your example shouldn't person 1 be week 4 and person 2 week 1 i.e. the last week which wasn't zero?

Do you want to return the actual date from the header row?

Try this formula in CB2 copied down

=LOOKUP(2,1/B2:CA2,B\$1:CA\$1)

format as date

#### barry houdini

##### MrExcel MVP
=INDEX(B1:CA1,MATCH(2,1/(INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0)<>0)))

Hi Domenic,

I assumed that a formula was required for each row, but to get the last date for a specific person, "Person1", you could use

=LOOKUP(2,1/INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0),B1:CA1)

#### Domenic

##### MrExcel MVP
=INDEX(B1:CA1,MATCH(2,1/(INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0)<>0)))

Hi Domenic,

I assumed that a formula was required for each row, but to get the last date for a specific person, "Person1", you could use

=LOOKUP(2,1/INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0),B1:CA1)

Hi Barry!

Since the data in this case will only contain whole numbers, your formula will definitely return the correct result. However, I still prefer my formula. It can be used when data includes decimal numbers. If needed elsewhere, the user can easily adopt it without regard to the data type. And unless I'm mistaken, any potential loss in efficiency is likely negligible.

#### Domenic

##### MrExcel MVP
Actually, in an effort to eliminate all risks, the following formula could be used instead...

=LOOKUP(9.99999999999999E+307,IF(INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0)<>0,INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0)),B1:CA1)

...confirmed with CONTROL+SHIFT+ENTER. This way, in addition to decimal numbers, the data can contain text which would need to be ignored.

#### barry houdini

##### MrExcel MVP
Hi Jay,

Sorry for the formula overkill, just one more from me, I promise

Hi Domenic,

Thanks for your previous comments. As you say my previous formula wouldn't work correctly if there were values between -0.5 and 0.5.

I believe you could use the following formula to ignore all text, zeroes and error values and return the date associated with the last numeric

=LOOKUP(9.9999999999999E+307,1/INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0),B1:CA1)

#### Domenic

##### MrExcel MVP
Hi Domenic,

Thanks for your previous comments. As you say my previous formula wouldn't work correctly if there were values between -0.5 and 0.5.

I believe you could use the following formula to ignore all text, zeroes and error values and return the date associated with the last numeric

=LOOKUP(9.9999999999999E+307,1/INDEX(B2:CA20000,MATCH("Person1",A2:A20000,0),0),B1:CA1)

Nice one, Barry! However, if I'm not mistaken, 1/Array is less efficient than using the IF statement...

Replies
0
Views
520
Replies
4
Views
579
Replies
2
Views
157
Replies
6
Views
337
Replies
2
Views
99

1,171,064
Messages
5,873,564
Members
432,984
Latest member
WilMel

### 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.

### Which adblocker are you using?

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

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