Solved

Format for Excel Lookup of Vlookup or Hlookup?

Posted on 2011-02-15
7
260 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

809 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