PDA

View Full Version : Assistance with Formula please (VLOOKUP/IF)



phendrena
12-08-2008, 09:13 AM
Hello,

I have a rather large worksheet of users, IDs, passwords, dealer numbers and dealer names. Not all of the user ids and passwords have been allocated. I have been given the missing ones as a seperate worksheet.

Now, without going down 700+ additions I would like to come up with a formula that'll do the job for me. If it was a simple VLOOKUP I wouldn't have any problems however, as the users can be assigned to several dealership (and have different user ids and passwords) I can't use a straight forward VLOOKUP.

So the formula should fill in the cell if the user name and also dealer code match.

Can anyone assist me with this one please?

Thanks,

The example sheet i've uploaded is a very basic example.

georgiboy
12-08-2008, 09:42 AM
What cell?

phendrena
12-08-2008, 09:48 AM
What cell?What cell do you want to know?

On the sheet i'm updaing (Sheet13) then the blank user ids are in column B, the blank passwords are column C. The username we match is Column A and the dealer number is column G.

We are pulling the data thats missing to complete the blanks from the sheet Dealership Users. Name to match is column B, dealer number to match is column A. The data is then puleld from columns C & D.

gearcutter
12-08-2008, 12:27 PM
Hi, I feel as though I am missing some thing here, but I can only work with what is in front of you. I used a vlookup and filled in the missing entries no bother I'm puzzled! have alook at the attachment
Regards Howard

Bob Phillips
12-08-2008, 12:37 PM
Obviously, adjust the ranges to suit

=IF(ISNA(MATCH(1,('Dealership Users'!$A$2:$A$4=Sheet13!G2)*('Dealership Users'!$C$2:$C$4=Sheet13!B2),0)),"",
INDEX('Dealership Users'!$D$2:$D$4,MATCH(1,('Dealership Users'!$A$2:$A$4=Sheet13!G2)*('Dealership Users'!$C$2:$C$4=Sheet13!B2),0)))

Array formula, so Ctrl-Shift-Enter it.

phendrena
12-09-2008, 02:09 AM
Thanks for the replies.
I do have another, small issue.... for some reason, when ever i enter a formula it enters the formula into the field rather than the result. I have to search and replace the '=' for the formula to work. I can't see any reason for this to happen. (As such i can't test your formula xld).

I've posted the full workbook with various bits of sensitive data removed - As you can see it's a little bit more complex than the basic example.

georgiboy
12-09-2008, 02:14 AM
Have you got the formulae cells formatted as text?

Bob Phillips
12-09-2008, 03:42 AM
georgiboy is correct, I forgot to mention that I had to reformat the security id column from text to general to get the formula to work.

phendrena
12-09-2008, 09:51 AM
formatted as text.... d'oh!