Solved

Format for Excel Lookup of Vlookup or Hlookup?

Posted on 2011-02-15
7
253 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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 46
Hlookup formula help 14 20
Access Excel export not behaving 2 25
Excel callender with date slider 5 27
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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

910 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

21 Experts available now in Live!

Get 1:1 Help Now