Complicated lookup

[Will_D]

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!

 

[Aladin Akyurek]

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]

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]

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]

Yes, they are all correct.

Thanks

 

[Aladin Akyurek]

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
	A	B	C	D
1
2
3
4
5
6
7
8	IBM
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
	N	O	P	Q
1		Company	Code
2		GOOGLE	Code-08
3		IBM	Code-09
4		INTEL	Code-05
5		MS	Code-07
6		ORACLE	Code-05
7		SHELL	Code-05
8		SUN MICROSYSTEMS	Code-04
Sheet2

And you want to retrieve the company code associated with Sheet1!A8 in A8 on Sheet2 and I suggested:

Book5
	A	B	C	D
1
2
3
4
5
6
7
8	Code-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]

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]

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"),"")

 

[Will_D]

Got it!

Many Thanks for for all your help!

Will