Hi guys,

I am having an incredibly frustrating time attempting to understand the
above functions.

My Set up is as follows:

Sheet 1 (S1), - list of clients that acted between certain dates

Column A, Client Unique ID [text]
Column B, Date which client first acted.

Sheet 2 (S2) - list of clients signed up between certain dates
Column A, Clients sign up date
Column B, Clients Unique ID [text]
Column E, =COUNTIF(Sheet1!A:A,B2) ~ Number of Times client appears
on sheet 1


My Aim for S2 Column F.

IF, Column E in S2 0, to look up the corresponding first action date
for that client in S1.

Atm the formula I would of though to work correctly is:

=IF(E10,VLOOKUP(B1,Sheet2!A:B,2),"Not Acted")

However this brings the wrong date.

Please can anyone advise on this matter and let me know where i am
gonig wrong.

Many thanks.
Sp


--
samprince
------------------------------------------------------------------------
samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=572290

================================================== =======
Vlookup gives wrong answer

Niek Otten, April 1 2006

Frequent causes:

1. Some cells look like numbers, but are actually text. You can check with the ISTEXT function.
Check both the search arguments and the lookup table.
Formatting as numbers afterwards doesn't help.
Remedy:
Format an empty cell as Number. Enter the number 1. EditCopy. Select your "numbers". EditPaste Special, check Multiply.

2. The data is not sorted ascending and the 4th argument of the VLOOKUP is TRUE or is omitted.

3. There are spaces or other invisible characters in either the search arguments or the lookup table.
This often happens when you import data from other applications.
Use the LEN() function to see how many characters there really are in the cell and compare that with what you see.
Use the TRIM function to remove all spaces except single spaces between words.
Use the CLEAN function to remove all nonprintable characters. HTML characters can be removed with a macro by David
McRitchie,
which can be downloaded he http://www.mvps.org/dmcritchie/excel/join.htm#trimall

4. The formula was copied from somewhere else, but the addresses of the lookup table were not absolute so have changed in
the Paste process
and now point to the wrong range.
Use absolute addresses for the lookup table, like $A$1:$B$20 instead of relative addresses like A1:B20.
When editing or entering a formula, use the F4 key to toggle between several forms of relative addresses and absolute ones
(normally only for the table, not for the search argument, but this depends on your specific problem).
The first hit of F4 changes the default relative address to an absolute one. That is usually what you need.
Even better: use a Defined Name for the table instead of cell addresses; InsertNameDefine.

5. The table was extended after its initial use, but the definition of the table in the VLOOKUP or in the Defined Name was
not adjusted accordingly.
To prevent this from happening: always use explicit bottom and top rows (with dummy error values if necessary)
and insert new rows or cells between those two.
Then the definition of the range or the Defined Name will adjust automatically.
Users of Excel 2003 and higher may profit from the List feature in the Data menu to maintain tables.
================================================== =======


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"samprince" wrote in message
...
|
| Hi guys,
|
| I am having an incredibly frustrating time attempting to understand the
| above functions.
|
| My Set up is as follows:
|
| Sheet 1 (S1), - list of clients that acted between certain dates
|
| Column A, Client Unique ID [text]
| Column B, Date which client first acted.
|
| Sheet 2 (S2) - list of clients signed up between certain dates
| Column A, Clients sign up date
| Column B, Clients Unique ID [text]
| Column E, =COUNTIF(Sheet1!A:A,B2) ~ Number of Times client appears
| on sheet 1
|
|
| My Aim for S2 Column F.
|
| IF, Column E in S2 0, to look up the corresponding first action date
| for that client in S1.
|
| Atm the formula I would of though to work correctly is:
|
| =IF(E10,VLOOKUP(B1,Sheet2!A:B,2),"Not Acted")
|
| However this brings the wrong date.
|
| Please can anyone advise on this matter and let me know where i am
| gonig wrong.
|
| Many thanks.
| Sp
|
|
| --
| samprince
| ------------------------------------------------------------------------
| samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
| View this thread: http://www.excelforum.com/showthread...hreadid=572290
|

DO not understand

#1, I'm looking trying to match text with text and return the date that
corresponds in the next colum. All are correctly formatted

#2, data is sorted in date order then user ID.

#3, there are no spaces in the user ID's

#4 & #5 I dont have a forumula yet, what i gace is my assumption of how
it should work but does not.


--
samprince
------------------------------------------------------------------------
samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=572290

<#2, data is sorted in date order then user ID

Then the 4th argument of your VLOOKUP should be FALSE instead of omitted (which defaults to TRUE)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"samprince" wrote in message
...
|
| DO not understand
|
| #1, I'm looking trying to match text with text and return the date that
| corresponds in the next colum. All are correctly formatted
|
| #2, data is sorted in date order then user ID.
|
| #3, there are no spaces in the user ID's
|
| #4 & #5 I dont have a forumula yet, what i gace is my assumption of how
| it should work but does not.
|
|
| --
| samprince
| ------------------------------------------------------------------------
| samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
| View this thread: http://www.excelforum.com/showthread...hreadid=572290
|

Forget about that one, not correct.
Sorry

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message ...
| <#2, data is sorted in date order then user ID
|
| Then the 4th argument of your VLOOKUP should be FALSE instead of omitted (which defaults to TRUE)
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "samprince" wrote in message
| ...
||
|| DO not understand
||
|| #1, I'm looking trying to match text with text and return the date that
|| corresponds in the next colum. All are correctly formatted
||
|| #2, data is sorted in date order then user ID.
||
|| #3, there are no spaces in the user ID's
||
|| #4 & #5 I dont have a forumula yet, what i gace is my assumption of how
|| it should work but does not.
||
||
|| --
|| samprince
|| ------------------------------------------------------------------------
|| samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
|| View this thread: http://www.excelforum.com/showthread...hreadid=572290
||
|
|