• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

Format for Excel Lookup of Vlookup or Hlookup?

I have an excel file with two worksheets


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?
Matt Pinkston
Matt Pinkston
1 Solution
Kalpesh ChhatralaSoftware ConsultantCommented:



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

Detailed Article

Hi, please post some example/dummy values and we will be able to help you better.

barry houdiniCommented:
Assuming your data starts at row 2 then try this formula in segment column row 2


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.


That will return a blank if neither ID is found

regards, barry
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Matt PinkstonAuthor Commented:
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
Matt PinkstonAuthor Commented:
excel 2010
barry houdiniCommented:
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


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

regards, barry
Matt PinkstonAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now