Excel 2007 : How to return multiple values using vlookup function in 2007

**lenkalondon** · 10-17-2011, 07:00 AM

hi anyone knows how to return multiple values from a vlookup function? possibly by using index function, but in a simpler way?

thanks a lot.
lenka

example of a spreadsheet attached.
function to be inserted - in sheet test1 column B
to look up in sheet test2 and return all relevant values from AgreementTypeID column.

**NBVC** · 10-17-2011, 07:56 AM

Try this formula:

=IFERROR(INDEX(test2!$B$2:$B$100,SMALL(IF(test2!$A$2:$A$100=A2,ROW(test2!$A$2:$A$100)-ROW(test2!$A$2)+1),COUNTIF(A$2:A2,A2))),"")

confirm it with CTRL+SHIFT+ENTER not just ENTER and then copy it down.