[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Format for Excel Lookup of Vlookup or Hlookup?

Posted on 2011-02-15
7
Medium Priority
?
273 Views
Last Modified: 2012-05-11
I have an excel file with two worksheets

Monthly
Data

in Monthly I have several columns:
columna is ID
columnb is ID2
Segment is the column I need to fill via lookup

in Data I have raw data
columna is ID
columnb is ID2
columnh is market segment

so I need a lookup that will do the following in Monthly

columnd "segment" will equal the lookup of data(columnh) if monthly columnb exists and there is a hit.

if columnb does not exist in data then use columnafor lookup agains columna

what type of lookup is this and how would it look?
0
Comment
Question by:Matt Pinkston
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 16

Expert Comment

by:Kalpesh Chhatrala
ID: 34896033
VLookup

=VLOOKUP("John",A1:M20,9)

HLookup

=HLOOKUP("Q1 2008", D1:Q20,3)

Detailed Article

http://ezinearticles.com/?VLOOKUP-and-HLOOKUP-Functions-in-Microsoft-Excel&id=1338597
0
 
LVL 6

Expert Comment

by:akajohn
ID: 34896043
Hi, please post some example/dummy values and we will be able to help you better.

A>
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34896082
Assuming your data starts at row 2 then try this formula in segment column row 2

=IF(ISNA(VLOOKUP(B2,Data!B$2:H$1000,7,0)),VLOOKUP(A2,Data!A$2:H$1000,8,0),VLOOKUP(B2,Data!B$2:H$1000,7,0))

You'll get an error if neither ID matches, you could further revise formula to return something else.

Which version of Excel are you using - above formula is universal but if you have Excel 2007 you can shorten, e.g.

=IFERROR(VLOOKUP(B2,Data!B$2:H$1000,7,0),IFERROR(VLOOKUP(A2,Data!A$2:H$1000,8,0),""))

That will return a blank if neither ID is found

regards, barry
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Matt Pinkston
ID: 34896170
worksheet Monthly
sforce   siebel    xxx   xxx   xxx   segment
111                     xxx   xxx   xxx  
222                     xxx   xxx   xxx
333                     xxx   xxx   xxx
              123       xxx   xxx   xxx
              456       xxx   xxx   xxx
              789       xxx   xxx   xxx

worksheet data
sforce   siebel    xxx   xxx   xxx   segment
111                     xxx   xxx   xxx   aaa
222                     xxx   xxx   xxx   bbb
333                     xxx   xxx   xxx   aaa
              123       xxx   xxx   xxx   bbb
              456       xxx   xxx   xxx   bbb
              789       xxx   xxx   xxx   ccc

so basically sforce or siebel should exist but if siebel does it should be the lookup in worksheet data, I want the segment value from data in monthly on a hit of siebel=siebel or sforce=sforce
0
 

Author Comment

by:Matt Pinkston
ID: 34896173
excel 2010
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 34896360
In Excel 2010 the 2nd formula I suggested should work. I assumed you had a maximum of 1000 rows in Data sheet, change as required

=IFERROR(VLOOKUP(B2,Data!B$2:H$1000,7,0),IFERROR(VLOOKUP(A2,Data!A$2:H$1000,8,0),""))

Is the data actually as shown, i.e. only one of the ID columns is populated per row?

regards, barry
0
 

Author Closing Comment

by:Matt Pinkston
ID: 35020163
excellent
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

649 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