Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup With multiple corresponding values
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup With multiple corresponding values
Hi,
The easiest solution is from main menu : Data Filter or AutoFilter ... HTH |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup With multiple corresponding values
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) -- Remember to click Yes if this is of some help Thanks, Shane Devenshire "Malcolm McMaster" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup With multiple corresponding values
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 "ShaneDevenshire" wrote: 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) -- Remember to click Yes if this is of some help Thanks, Shane Devenshire "Malcolm McMaster" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup With multiple corresponding values
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),IN DEX(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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup With multiple corresponding values
many thanks I'll give this a try
"Carim" wrote: 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),IN DEX(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup multiple values - sum returned values into single cell | Excel Worksheet Functions | |||
Vlookup with multiple corresponding values | Excel Worksheet Functions | |||
vlookup on multiple values | Excel Worksheet Functions | |||
vlookup - multiple values | Excel Worksheet Functions | |||
vlookup, multiple values, sum values into one cell?? | Excel Worksheet Functions |