Link to home
Start Free TrialLog in
Avatar of Matt Pinkston
Matt Pinkston

asked on

Format for Excel Lookup of Vlookup or Hlookup?

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?
Avatar of Kalpesh Chhatrala
Kalpesh Chhatrala
Flag of India image

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
Hi, please post some example/dummy values and we will be able to help you better.

A>
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
Avatar of Matt Pinkston
Matt Pinkston

ASKER

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
excel 2010
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
excellent