Hello,

Could you please help design a lookup formula as shown in the attached spreadsheet ?
Thanks

Dumbo

On Thursday, April 11, 2013 4:44:54 PM UTC-7, Excel Dumbo wrote:
Hello,



Could you please help design a lookup formula as shown in the attached

spreadsheet ?

Thanks



Dumbo





+-------------------------------------------------------------------+

|Filename: Complicated Lookup.zip |

|Download: http://www.excelbanter.com/attachment.php?attachmentid=841|

+-------------------------------------------------------------------+







--

Excel Dumbo

In C2:

=VLOOKUP(A2,A2:B6,2,0)

A2 = TM72001. Instead of A2 as the lookup value, you could have a drop down list in say F1 with all the TM..... numbers.

Then the formula would look like this.
=VLOOKUP(F1,A2:B6,2,0)

Regards,
Howard

I recommend that you learn how to design the data layouts so working
with them is less complicated, more intuitive, and so your projects
make for better productivity.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Thursday, April 11, 2013 4:44:54 PM UTC-7, Excel Dumbo wrote:
Hello,



Could you please help design a lookup formula as shown in the attached

spreadsheet ?

Thanks



Dumbo





+-------------------------------------------------------------------+

|Filename: Complicated Lookup.zip |

|Download: http://www.excelbanter.com/attachment.php?attachmentid=841|

+-------------------------------------------------------------------+







--

Excel Dumbo

Maybe I missread your problem.

Try this in C2

=VLOOKUP(LEFT(F1,7),A2:B6,2,0)

Where TM72001-S1-K.Cantlon is in F1.

Does that do it?

Howard

Hi,

Am Thu, 11 Apr 2013 23:44:54 +0000 schrieb Excel Dumbo:

Could you please help design a lookup formula as shown in the attached
spreadsheet ?
Thanks
for this structure of a table you need two nested index/match formulas:
try:
=INDEX('Look up Data'!$B$19:$B$22,MATCH("PSR Rank "&INDEX('Look up Data'!$B$2:$B$12,MATCH(A2,LEFT('Look up Data'!$A$2:$A$12,7),0)),'Look up Data'!$A$19:$A$22,0))
and enter the array formula with CTRL+Shift+Enter

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi again,

Am Fri, 12 Apr 2013 09:23:58 +0200 schrieb Claus Busch:

=INDEX('Look up Data'!$B$19:$B$22,MATCH("PSR Rank "&INDEX('Look up Data'!$B$2:$B$12,MATCH(A2,LEFT('Look up Data'!$A$2:$A$12,7),0)),'Look up Data'!$A$19:$A$22,0))
and enter the array formula with CTRL+Shift+Enter

a little bit shorter:
=VLOOKUP("PSR Rank "&VLOOKUP(A2,LEFT('Look up Data'!$A$2:$B$12,7),2,0),'Look up Data'!$A$19:$B$22,2,0)
and also enter with CTRL+Shift+Enter

or without array:
=SUMPRODUCT(--('Look up Data'!$A$19:$A$22="PSR Rank "&SUMPRODUCT(--(LEFT('Look up Data'!$A$2:$A$12,7)=A2),'Look up Data'!$B$2:$B$12)),'Look up Data'!$B$19:$B$22)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

THanks a lot Claus. It worked. Thanks heaps

Thanks to Howard too for the input.