lookup multiple values
 

Assuming the following table of categories and values:
A B
1 cat1 val1
2 cat2 val2
3 cat3 val3
4 cat1 val4
5 cat2 val5

I need to lookup multiple values for the same category: Ex: if in cell C1 i
lookup cat1 it returns val1, then if in cell D1 i lookup cat1 it returns val4.

Thanks in advance for any help.

lookup multiple values
 

In C1
=INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5="cat1",ROW($A$ 1:$A$5)),COLUMN(A$1)),1)

Entered with Ctrl+shift+Enter rather than just enter. then select C1 and
drag fill to the right until you get an error returned.

--
Regards,
Tom Ogilvy


"confused" wrote:

Assuming the following table of categories and values:
A B
1 cat1 val1
2 cat2 val2
3 cat3 val3
4 cat1 val4
5 cat2 val5

I need to lookup multiple values for the same category: Ex: if in cell C1 i
lookup cat1 it returns val1, then if in cell D1 i lookup cat1 it returns val4.

Thanks in advance for any help.

lookup multiple values
 

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
array enter into C1:D1

=Transpose(VLookups("cat1",a1:b5,2))

Alan Beban

confused wrote:
Assuming the following table of categories and values:
A B
1 cat1 val1
2 cat2 val2
3 cat3 val3
4 cat1 val4
5 cat2 val5

I need to lookup multiple values for the same category: Ex: if in cell C1 i
lookup cat1 it returns val1, then if in cell D1 i lookup cat1 it returns val4.

Thanks in advance for any help.