Solved

lookup

Posted on 2011-02-24
9
301 Views
Last Modified: 2012-05-11
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
Comment
Question by:Matt Pinkston
  • 5
  • 3
9 Comments
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
 
LVL 6

Expert Comment

by:TinTombStone
Comment Utility
Or a VLookup perhaps

this in B2
=VLOOKUP(A2,data1!$A$2:$B$5,2,FALSE)
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Matt Pinkston
Comment Utility
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
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
 

Author Comment

by:Matt Pinkston
Comment Utility
works perfetc but it throughs the results in between two cells which is kindof weird
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:Matt Pinkston
Comment Utility
perfect!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now