How do I return Multiple values using VLookup?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sean - 12 Jun 2007 06:12 GMT

Hey,

I have a spreadsheet running while using VLookup, now in the first row I
have multiple dollar values but different account names that relate to the
dollar value is there anyway to return the other account names that have the
same dollar value?

The function looks like ;
=VLOOKUP(A1,'01.06.07'!$A$1:$C$2000,2,FALSE)

and the multiples would be like I need to return column 1 and 4, so I have
the multiples.

29.8 1942978 - ACCUFIT 1942978 NETBANK TFR
29.8 1109582 - ACCOUNT 1109582 DIACORE PTY LT

Can anyone help with this?

Reply to this Message

Domenic - 12 Jun 2007 12:45 GMT

To return the corresponding values for the second column, Column B, try
the following...

B1:

=COUNTIF('01.06.07'!A1:A2000,A1)

C1, copied down:

=IF(ROWS(C$1:C1)<=$B$1,INDEX('01.06.07'!$A$1:$C$2000,SMALL(IF('01.06.07'!
$A$1:$A$2000=$A$1,ROW('01.06.07'!$A$1:$A$2000)-ROW('01.06.07'!$A$1)+1),RO
WS(C$1:C1)),2),"")

...confirmed with CONTROL+SHIFT+ENTER. To return the corresponding
values for the 3rd column, change the 2 at the end of the formula to a 3.

Hope this helps!

> Hey,
>
[quoted text clipped - 13 lines]
>
> Can anyone help with this?

Reply to this Message