Solved

Format for Excel Lookup of Vlookup or Hlookup?

Posted on 2011-02-15
7
249 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

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

Accepted Solution

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

11 Experts available now in Live!

Get 1:1 Help Now