Complicated lookup

Will_D

Board Regular
Joined
Mar 6, 2005
Messages
195
On sheet1 in cell A8 the user will chose from a list of companies that are listed in sheet2 O2:O33. I would like sheet2 cell A8 to show the corresponding company code that is listed in sheet2 P2:P33. So if the user entered the company that is in sheet2 O5 then sheet2 A8 would show the code in sheet2 P5.

Many Thanks!
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Will_D said:
On sheet1 in cell A8 the user will chose from a list of companies that are listed in sheet2 O2:O33. I would like sheet2 cell A8 to show the corresponding company code that is listed in sheet2 P2:P33. So if the user entered the company that is in sheet2 O5 then sheet2 A8 would show the code in sheet2 P5.

Many Thanks!

If O2:P33 is sorted in ascending order on column O...

In A8 on Sheet2 enter:

=IF(LOOKUP(Sheet1!A8,$O$2:$O$33)=Sheet1!A8,LOOKUP(Sheet1!A8,$O$2:$P$33),"Not Found")

Otherwise:

=INDEX($P$2:$P$33,MATCH(Sheet1!A8,$O$2:$O$33,0))
 

Will_D

Board Regular
Joined
Mar 6, 2005
Messages
195
Thanks for responding! I tried both of those and a box pops up to Update Values: Report. I tried canceling that and linking that to the sheet but i get a #NAME? error either way.

Thanks
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Will_D said:
Thanks for responding! I tried both of those and a box pops up to Update Values: Report. I tried canceling that and linking that to the sheet but i get a #NAME? error either way.

Thanks

Did you adjust the formula for ranges/shett names it refers to wrt your actual data?
 

Will_D

Board Regular
Joined
Mar 6, 2005
Messages
195

ADVERTISEMENT

Yes, they are all correct.

Thanks
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Will_D said:
Yes, they are all correct.

Thanks

I guess you did not get the intended results...

Recap:

You have a dropdown list of companies in A8 on Sheet1...
Book5
ABCD
1
2
3
4
5
6
7
8IBM
9
10
Sheet1


As you see, IBM is selected.

You have a table of companies along with their associated codes in O2:P33 on Sheet2. And this table is sorted in ascending order on column O, right?
Book5
NOPQ
1CompanyCode
2GOOGLECode-08
3IBMCode-09
4INTELCode-05
5MSCode-07
6ORACLECode-05
7SHELLCode-05
8SUN MICROSYSTEMSCode-04
Sheet2


And you want to retrieve the company code associated with Sheet1!A8 in A8 on Sheet2 and I suggested:
Book5
ABCD
1
2
3
4
5
6
7
8Code-09
9
10
Sheet2


The formula in A8 is:

=IF(LOOKUP(Sheet1!A8,$O$2:$O$33)=Sheet1!A8,LOOKUP(Sheet1!A8,$O$2:$P$33),"Not Found")

So, what is the problem that you seem to have?
 

Will_D

Board Regular
Joined
Mar 6, 2005
Messages
195

ADVERTISEMENT

OK it is working, the only problem I am having now is when sheet1 A8 is blank sheet2 A8 shows #N/A, is there any way for Shhet2 A8 to be blank when sheet1 A8 is empty.

Thanks for your help!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Will_D said:
OK it is working, the only problem I am having now is when sheet1 A8 is blank sheet2 A8 shows #N/A, is there any way for Shhet2 A8 to be blank when sheet1 A8 is empty.

Thanks for your help!

=IF(Sheet1!A8<>"",IF(LOOKUP(Sheet1!A8,$O$2:$O$33)=Sheet1!A8,LOOKUP(Sheet1!A8,$O$2:$P$33),"Not Found"),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,293
Members
414,440
Latest member
Kim0204

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
Top