VLookup With multiple corresponding values

  • Thread starter Thread starter Malcolm McMaster
  • Start date Start date
M

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
 
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)
 
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
 
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
 
Back
Top