Need a help with a forumula

Stormy4757

New member
Joined
Dec 26, 2013
Messages
11
Reaction score
0
Points
0
I need a vlookup formula or whatever formula to do the following to the attached spreadsheet
The first worksheet is the sheet I want to pull information into. I want to search the2nd worksheet, User Profile, in the PID column, for each of the PIDs in the first worksheet (column B). I then want to pull that whole line of information into the Columns C through X. I have ensured that the first two will have results. Not all PIDs will be found.
I hope I am making sense. My manager tells me she wants this for her 3 o’clock meeting tomorrow (Wednesday). I am not a happy camper cause this has 50,000 some lines of data. Can any one please help me?

View attachment Test File.xlsx
 

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
In cell C2, try this:

Code:
=INDEX('User Profile'!$A$2:$V$14,MATCH($B2,'User Profile'!$D$2:$D$14,0),MATCH(C$1,'User Profile'!$A$1:$V$1,0))

Amend rows/columns as required for the size of the second worksheet, then autofill across/down.
 

Stormy4757

New member
Joined
Dec 26, 2013
Messages
11
Reaction score
0
Points
0
CheshireCat,

Thank you for your efforts. I tried inputting the formula several times and I get #N/A.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
#N/A means no match, so the code isn't found.

You could also try

=VLOOKUP(B3,CHOOSE({1,2},'User Profile'!$D$2:$D$14,'User Profile'!$A$2:$A$14),2,FALSE)
 

Stormy4757

New member
Joined
Dec 26, 2013
Messages
11
Reaction score
0
Points
0
Bob,

Yes, I know #N/A means no match. I see it all too often. I tried your formula, typed it in as you presented it above, and I get #N/A. Maybe this process just isn't possible. It has given me a headache and I hate this last minute stuff. I mean this could be done with a few days to do it, since there are over 50,000 lines of data, I would do it manually. But she wants it done right now, her way, with a VLookup.

Thanks for your efforts!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
You are getting #N/A because there is nothing on User profile for some of the codes. B£ doesn't match, B4 does. If you know what it means, what are you complaining about? What do want it to return when there is no match, it can't conjure up some fake value for you.
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
I see that this issue is closed. However, for other people that want to know, if you expect to have results that are not listed in your lookup table thus returning a #N/A; you can get rid of this by simply adding an iferror() around your original formula.

Code:
=IFERROR(INDEX('User Profile'!$A$2:$V$14,MATCH($B5,'User Profile'!$D$2:$D$14,0),MATCH(C$1,'User Profile'!$A$1:$V$1,0)),"")

this way if it returns an error it will simply report "" or blank text in the cell. This is useful if you just don't want to see a bunch of #N/A values.
 
Top