Formula to match a value and return multiple results?

[haste]

Hi there,

I'm looking for a formula to match a value within an array and return the results found.

I know how to you use vlookup/match/index formula's, but I can only return the first match found. I would like to return the first match in cell A1 and the second match inside A2, third inside A3, etc.

So when the first match is found and placed inside A1 then the formula inside A2 should ignore the first match that is already found and show the next match value.

I would like to get this done with a formula and not with the autofilter option inside Excel.

I created an example: http://www.haste.nl/formula-filter.zip

Thanks for helping out!

 

[haste]

You guys are amazing!
It's working again :D

Thanks a lot for the support!!

 

[T. Valko]

You guys are amazing!
It's working again :D

Thanks a lot for the support!!

You're welcome. Thanks for the feedback!

 

[Marcelo Branco]

You guys are amazing!
It's working again :D

Thanks a lot for the support!!

You are welcome. Glad for help

 

[MsTin]

Hi there,
I'm new to this forum and to Excel.
I'm looking for a formula that lookup an array after data is entered into a specific cell and return the corresponding results from multiple rows (as the data can be repeated in that same column)

I know a little about vlookup/match/index formula's, but I can only return the first match found. I would like to return all the matches found. I hope to get this done with a formula and not with the autofilter option inside Excel. [TABLE="width: 348"]
<tbody>[TR]
[TD="colspan: 2"]Below is an example of the Query form. Eg. User just enter the NRIC in the field and the formula would auto lookup the table and present the corresponding result as follows:[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NRIC[/TD]
[TD]S7898829H[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Name:[/TD]
[TD]Julie[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Type of Medication[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Drug register[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]NRIC[/TD]
[TD]Name[/TD]
[TD]Medication[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]06/06/09[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]S7898829H[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 164"]
<tbody>[TR]
[TD]Julie[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]Mefenamic acid 250mg 2 cap tds[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]08/09/01[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]S7512835K[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 164"]
<tbody>[TR]
[TD]Timothy[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]Metronidazole 200mg 1 cap tds[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]08/06/09[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]S7310035H[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 164"]
<tbody>[TR]
[TD]Philip[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]Amoxycillin 250mg 2 cap tds[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]09/06/09[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]S5672900I[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 164"]
<tbody>[TR]
[TD]Peter[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]Arcoxia 90mg o.m[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]10/06/09[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]S7898829H[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 164"]
<tbody>[TR]
[TD]Julie[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]Arcoxia 90mg o.m[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]09/05/12[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]S7898829H[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 164"]
<tbody>[TR]
[TD]Julie[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]Fugentin 625mg 1 tab b.d[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MsTin]

Thanks in advance for any help... I had trouble attaching file in this forum, I hope the "table" helps.

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Say your data is in Sheet1 like this


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Date​

[/TD]
[TD]

NRIC​

[/TD]
[TD]

Name​

[/TD]
[TD]

Medication​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

06/06/2009​

[/TD]
[TD]

S7898829H​

[/TD]
[TD]

Julie​

[/TD]
[TD]

Mefenamic acid 250mg 2 cap tds​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

08/09/2001​

[/TD]
[TD]

S7512835K​

[/TD]
[TD]

Timothy​

[/TD]
[TD]

Metronidazole 200mg 1 cap tds​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

08/06/2009​

[/TD]
[TD]

S7310035H​

[/TD]
[TD]

Philip​

[/TD]
[TD]

Amoxycillin 250mg 2 cap tds​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

09/06/2009​

[/TD]
[TD]

S5672900I​

[/TD]
[TD]

Peter​

[/TD]
[TD]

Arcoxia 90mg o.m​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

10/06/2009​

[/TD]
[TD]

S7898829H​

[/TD]
[TD]

Julie​

[/TD]
[TD]

Arcoxia 90mg o.m​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

09/05/2012​

[/TD]
[TD]

S7898829H​

[/TD]
[TD]

Julie​

[/TD]
[TD]

Fugentin 625mg 1 tab b.d​

[/TD]
[/TR]
</TBODY>[/TABLE]



In Sheet2


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

NRIC​

[/TD]
[TD]

S7898829H​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Name:​

[/TD]
[TD]

Julie​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Date​

[/TD]
[TD]

Type of Medication​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

06/06/2009​

[/TD]
[TD]

Mefenamic acid 250mg 2 cap tds​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

10/06/2009​

[/TD]
[TD]

Arcoxia 90mg o.m​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

09/05/2012​

[/TD]
[TD]

Fugentin 625mg 1 tab b.d​

[/TD]
[/TR]
</TBODY>[/TABLE]



Put the NRIC of interest in B1

Formula in B2
=VLOOKUP(B1,Sheet1!B:C,2,0)

Array formula in A4 copied down
=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$B$2:$B$100=$B$1,ROW(Sheet1!$B$2:$B$100)),ROWS(A$4:A4))),"")

confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

Format A4, A5,....as Date

Array formula in B4 copied down

=IFERROR(INDEX(Sheet1!D:D,SMALL(IF(Sheet1!$B$2:$B$100=$B$1,ROW(Sheet1!$B$2:$B$100)),ROWS(B$4:B4))),"")

confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

If you are not familiar with array formulas take a look at
Array Formulas

Hope this helps

M.

 

[MsTin]

Wow! Thank you so much, Marcelo Branco!
You are amazing and this is really great!
I'm impressed! I took several days to search around the net and experiment with formulas but to no avail.
thanks so much again!!!

MsTin

Hi and welcome to Mr Excel Forum

Say your data is in Sheet1 like this


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Date​

[/TD]
[TD]

NRIC​

[/TD]
[TD]

Name​

[/TD]
[TD]

Medication​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

06/06/2009​

[/TD]
[TD]

S7898829H​

[/TD]
[TD]

Julie​

[/TD]
[TD]

Mefenamic acid 250mg 2 cap tds​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

08/09/2001​

[/TD]
[TD]

S7512835K​

[/TD]
[TD]

Timothy​

[/TD]
[TD]

Metronidazole 200mg 1 cap tds​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

08/06/2009​

[/TD]
[TD]

S7310035H​

[/TD]
[TD]

Philip​

[/TD]
[TD]

Amoxycillin 250mg 2 cap tds​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

09/06/2009​

[/TD]
[TD]

S5672900I​

[/TD]
[TD]

Peter​

[/TD]
[TD]

Arcoxia 90mg o.m​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

10/06/2009​

[/TD]
[TD]

S7898829H​

[/TD]
[TD]

Julie​

[/TD]
[TD]

Arcoxia 90mg o.m​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

09/05/2012​

[/TD]
[TD]

S7898829H​

[/TD]
[TD]

Julie​

[/TD]
[TD]

Fugentin 625mg 1 tab b.d​

[/TD]
[/TR]
</TBODY>[/TABLE]



In Sheet2


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

NRIC​

[/TD]
[TD]

S7898829H​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Name:​

[/TD]
[TD]

Julie​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Date​

[/TD]
[TD]

Type of Medication​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

06/06/2009​

[/TD]
[TD]

Mefenamic acid 250mg 2 cap tds​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

10/06/2009​

[/TD]
[TD]

Arcoxia 90mg o.m​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

09/05/2012​

[/TD]
[TD]

Fugentin 625mg 1 tab b.d​

[/TD]
[/TR]
</TBODY>[/TABLE]



Put the NRIC of interest in B1

Formula in B2
=VLOOKUP(B1,Sheet1!B:C,2,0)

Array formula in A4 copied down
=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$B$2:$B$100=$B$1,ROW(Sheet1!$B$2:$B$100)),ROWS(A$4:A4))),"")

confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

Format A4, A5,....as Date

Array formula in B4 copied down

=IFERROR(INDEX(Sheet1!D:D,SMALL(IF(Sheet1!$B$2:$B$100=$B$1,ROW(Sheet1!$B$2:$B$100)),ROWS(B$4:B4))),"")

confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

If you are not familiar with array formulas take a look at
Array Formulas

Hope this helps

M.

 

[Marcelo Branco]

Ms Tin

You are welcome and thanks for the feedback.

M.

 

[Jason248]

You are amazing !!!

I'm looking for a formula to calculate which box # to use when shipping an item/items after the dimensions are entered in excel,


I have 2 sheets one sheet has the dimensions of the box sizes we carry and the dimensions of the boxes that are available , and the other sheet has the items and the dimensions that we have in stock ,


I would like enter the dimensions of the item or the the item # and excel should give me the best/smallest box size to use when shipping the item , keep in mind the box has to be bigger then the item we're trying to ship.






Example :

[TABLE="width: 482"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 316"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Sheet 1 with 4 Box sizes[TABLE="width: 316"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]
[TABLE="width: 291"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]Box C4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]5.25[/TD]
[TD]Box 21[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD]8.75[/TD]
[TD]Box 30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]14[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]Box 34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2 .. 8 items with the dimensions.

[TABLE="width: 438"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]
[TABLE="width: 511"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD](item)[/TD]
[TD](need the box # from sheet 1)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3.5[/TD]
[TD]2.2[/TD]
[TD]3.3[/TD]
[TD]crc 1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7.2[/TD]
[TD]6.5[/TD]
[TD]5[/TD]
[TD]crc 2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10.9[/TD]
[TD]9.4[/TD]
[TD]7.8[/TD]
[TD]crc 3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11.7[/TD]
[TD]11.3[/TD]
[TD]7.5[/TD]
[TD]crc 4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]crc 5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]3.5[/TD]
[TD]3[/TD]
[TD]crc 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]9.5[/TD]
[TD]9.5[/TD]
[TD]7.7[/TD]
[TD]crc 7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]crc 8[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 511"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

if i would enter the [FONT=arial, sans-serif]dimensions of the item i'm looking to ship , the formula will look in [/FONT]sheet 1[FONT=arial, sans-serif] for the smallest box that the item will fit in and enter the box# in column E.

Thanks in advance
Jason[/FONT]

 

[Marcelo Branco]

Try this array formula in E1 copied down

=INDEX(Sheet1!D:D,SMALL(IF(Sheet1!$A$1:$A$4>=A1,IF(Sheet1!$B$1:$B$4>=B1,IF(Sheet1!$C$1:$C$4>=C1,ROW(Sheet1!$D$1:$D$4)))),1))

confirmed with Ctrl+Shift+Enter

Hope this helps

M.