lookup functions....

G

ganeshpoojary05

Board Regular
Joined
Apr 26, 2011
Messages
105
Hi all, i need a formula for lookup.

Example:

First Sheet
Column A
the employee zerry is working as a businessanalyst
jack is good guy
microsoft hired jim
respondez sam is an employee


The below data are in different sheet.
Column A
sam
zerry
jack
jim

Column B
engineer
business analyst
agent
manager


So i need a formula where in my column B in the first sheet considering lookup value from second sheet, it should return a value as below:
Column B
business analyst
agent
manager
engineer

 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
=IF(ISNUMBER(SEARCH("zerry",A1)),"business analyst",IF(ISNUMBER(SEARCH("jack",A1)),"agent",IF(ISNUMBER(SEARCH("jim",A1)),"manager",IF(ISNUMBER(SEARCH("sam",A1)),"engineer"))))
 
Upvote 0
hi, i need a formula in column b in the first sheet using the lookup table from the second sheet. for example, the range(a1) in the first sheet contains the value as "the employee zerry is working as a businessanalyst". so it should take zerry and look up from second sheet and should return a value as "business analyst".
 
Upvote 0
Try
=IF(ISNUMBER(SEARCH(Sheet2!$A$1,A1)),Sheet2!$B$1,IF(ISNUMBER(SEARCH(Sheet2!$A$2,A1)),Sheet2!$B$2,IF(ISNUMBER(SEARCH(Sheet2!$A$3,A1)),Sheet2!$B$3,IF(ISNUMBER(SEARCH(Sheet2!$A$4,A1)),Sheet2!$B$4))))
 
Upvote 0
Try

=LOOKUP(2^15,SEARCH('Second Sheet'!$A$1:$A$4,A1),'Second Sheet'!$B$1:$B$4)


First Sheet


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 356px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana">the employee zerry is working as a businessanalyst</TD><TD>business analyst</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana">jack is good guy</TD><TD>agent</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana">microsoft hired jim</TD><TD>manager</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Verdana">respondez sam is an employee</TD><TD>engineer</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=LOOKUP(2^15,SEARCH('Second Sheet'!$A$1:$A$4,A1),'Second Sheet'!$B$1:$B$4)</TD></TR><TR><TD>B2</TD><TD>=LOOKUP(2^15,SEARCH('Second Sheet'!$A$1:$A$4,A2),'Second Sheet'!$B$1:$B$4)</TD></TR><TR><TD>B3</TD><TD>=LOOKUP(2^15,SEARCH('Second Sheet'!$A$1:$A$4,A3),'Second Sheet'!$B$1:$B$4)</TD></TR><TR><TD>B4</TD><TD>=LOOKUP(2^15,SEARCH('Second Sheet'!$A$1:$A$4,A4),'Second Sheet'!$B$1:$B$4)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Second Sheet


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 68px"><COL style="WIDTH: 117px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana">sam</TD><TD style="FONT-FAMILY: Verdana">engineer</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana">zerry</TD><TD style="FONT-FAMILY: Verdana">business analyst</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana">jack</TD><TD style="FONT-FAMILY: Verdana">agent</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Verdana">jim</TD><TD style="FONT-FAMILY: Verdana">manager</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



Credit to Barry Houdini from the challenge of the month June/July 2008
Post #5
http://www.mrexcel.com/forum/showthread.php?p=1597483#post1597483
 
Last edited:
Upvote 0
ganeshpoojary05 said:
Hi all, i need a formula for lookup.

Example:

First Sheet
Column A
the employee zerry is working as a businessanalyst
jack is good guy
microsoft hired jim
respondez sam is an employee


The below data are in different sheet.
Column A
sam
zerry
jack
jim

Column B
engineer
business analyst
agent
manager


So i need a formula where in my column B in the first sheet considering lookup value from second sheet, it should return a value as below:
Column B
business analyst
agent
manager
engineer
Click to expand...
Try this...

Book1
AB
2samengineer
3zerrybusiness analyst
4jackagent
5jimmanager
Sheet2

Book1
AB
2the employee zerry is working as a businessanalystbusiness analyst
3jack is good guyagent
4microsoft hired jimmanager
5respondez sam is an employeeengineer
Sheet1

Formula entered in B2:

=LOOKUP(1E100,SEARCH(Sheet2!A$2:A$5,A2),Sheet2!B$2:B$5)

Copy down as needed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
INDEX-MATCH with a vertical lookup
Replies
4
Views
583
Darren_workforce
D
J
  • Solved
Multi-criteria lookup
Replies
3
Views
293
Asbestos_Jen
Asbestos_Jen
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
125
BenW71
B
H
  • Question Question
Using XLOOKUP in VBA to lookup value underneath specific header name (columns can change depending on worksheet)
Replies
0
Views
164
Hailee994
H
B
  • Solved
IFS (?) Formula help
Replies
4
Views
299
etaf
etaf

Forum statistics

Threads
1,224,598
Messages
6,179,822
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top