VLookup With multiple corresponding values

[Malcolm McMaster]

How can I utilise the VLookup command to return multiple values. I would
like to be able to enter a supplier code and have a formula that will match
the code from an area where invoices are entered and return all the invoice
numbers and amounts for the matched criteria.
Any ideas would most welcome.

Malcolm

 

[Carim]

Hi,

The easiest solution is from main menu : Data Filter or AutoFilter ...

HTH

 

[ShaneDevenshire]

Hi,

It seems unlikely that you would want to return both pieces of info to the
same cell, nor would you want to add them. So the best solution would be two
VLOOKUP formulas.

Suppose your supplier code is entered into A1 and you want the info returned
to B1 and C1. Finally suppose the table of info is in the range F1:H100 with
the first column containing supplier codes.
=VLOOKUP(A1,F1:H100,2,False)
=VLOOKUP(A1,F1:H100,3,False)

Technically you could return both results to a single cell:
=VLOOKUP(A1,F1:H100,2,False)&" "&VLOOKUP(A1,F1:H100,3,False)

 

[Malcolm McMaster]

Hi Shane,

I probably should have more specific.

I have a worksheet that contains the data entered from suppliers invoices,
what I would like to acheive is from another sheet or location, I would like
to enter a supplier code and have all invoices that are matched to the
supplier code returned and populate a form that will be formatted as a
remittance advise

 

[Carim]

Hi Malcolm,

What you are after is only possible with an array formula ( Control
Shift Enter ), starting in row 2 of your second worksheet :

=IF(ROWS(B$2:B2)<=COUNTIF(Data!$A$2:$A$21,$A$2),INDEX(Data!$B$2:$B
$21,SMALL(IF(Data!$A$2:$A$21=$A$2,ROW(Data!$A$2:$A$21)-ROW(Data!$A
$2)+1),ROWS(B$2:B2))),"")

Assumption is that your data is located is located in sheet called
Data, area is A1:B21 and field names are in row 1 ...

HTH

 

[Malcolm McMaster]

many thanks I'll give this a try