Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

lookup

I have two worksheets

One called data1 with the following columns
ID
WBS ID

Second is called data1 with the following columns
ID
WBS <---- empty needs to be filled from data 1 via lookup
0
Matt Pinkston
Asked:
Matt Pinkston
  • 5
  • 3
1 Solution
 
barry houdiniCommented:
It's a little difficult to work out your setup from that description....

If data1 has WBS in column A and IDs in column B then in the other sheet, if A2 has an ID use this formula in B2

=INDEX(Data!A:A,MATCH(A2,Data!B:B,0))

regards, barry
0
 
barry houdiniCommented:
sorry, I meant the sheet name to be data1 not data......and I should also include some error handling so formula should be

=IFERROR(INDEX(data1!A:A,MATCH(A2,data1!B:B,0)),"No match")

see attached

regards, barry
26846411.xlsx
0
 
TinTombStoneCommented:
Or a VLookup perhaps

this in B2
=VLOOKUP(A2,data1!$A$2:$B$5,2,FALSE)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
barry houdiniCommented:
I wasn't sure which way round the columns were TinTombStone. A standard VLOOKUP won't be possible if the data to be returned is in a column to the left of the data to be matched.....whereas INDEX/MATCH can work either way round....

regards, barry
0
 
Matt PinkstonAuthor Commented:
let me be more specific

first worksheet (codes)
column E has ID
column B has WBS

second worksheet (Monthly)
column B has ID
coulmn E needs WBS from codes on match of IDs
0
 
barry houdiniCommented:
Then you need INDEX/MATCH as suggested, try this in E2 on Monthly sheet

=IFERROR(INDEX(codes!B:B,MATCH(B2,codes!E:E,0)),"No match")

then copy formula down column

regards, barry
0
 
Matt PinkstonAuthor Commented:
works perfetc but it throughs the results in between two cells which is kindof weird
0
 
barry houdiniCommented:
I think that sometimes happens when you copy formulas from directly from here, it affects the formatting, you might have to change the font and/or "left justify" the data....

regards, barry
0
 
Matt PinkstonAuthor Commented:
perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now