Alternate formula for VLOOKUP in reporting.

**luccallens** · 11-05-2013, 03:27 AM

Hi,

I have a master sheet holding outstanding payables for many associates. Every associate likes to know his/her outstanding amounts. VLOOKUP is not really helpful in this case since copying the formula will return the same data over and over till the end of the array is reached. I want every relevant line only once of course. Any alternative to VLOOKUP
Example of formula used: =VLOOKUP($A$6,'[test AP HK.xlsx]AP'!$A3:$K$1000,10,false). So the value in column 10 I want only once, then I want the next value in the cell under it for $A$6.

Thanks guys.

**canapone** · 11-05-2013, 03:32 AM

Hi,

maybe (in column K values)

=SUMIF('[test AP HK.xlsx]AP'!$A3:$A$1000$,A6,'[test AP HK.xlsx]AP'!$K3:$K$1000)

or

=SUMIFS('[test AP HK.xlsx]AP'!$K3:$K$1000,'[test AP HK.xlsx]AP'!$A3:$A$1000$,A$6,......

if you would need to add other conditions.

If you could share a sample file, it will be easier to help you.

Hope it helps

**canapone** · 11-05-2013, 03:49 AM

Hi again

if you'd need a list of numbers, an array formula to be confirmed with control+shift+enter

=IFERROR(INDEX('[TEST AP HK.xlsx]AP'!$K$3:$K$1000,SMALL(IF('[TEST AP HK.xlsx]AP'!$A$3:$A$1000=$A$6,ROW($A$3:$A$1000)-2),ROWS($A$1:A1))),"")

Hope it helps