## Lookup and return multiple values on the same row from a range excluding blanks

I think I can use this to help me populate a calendar.I substituted dates for Pen, Paper, and Eraser. I then had locations substituted for $ values.Where I have a date of say, 11/27/12, I have 10 locations delivering that day. Using the template as shown in the screen shot under "Retun multiple values horizontally or vertically (vba)" I cannot expand past column "C" to return multiple values.I think it is in the array code but I cannot figure out how to return values past column C.

If you can help, greatly appreciated!

Thanks,

Jim

**Answer:**

**Array formula in cell B10:**

Recommended article

Vlookup a cell range and return multiple values

VLOOKUP a multi-column range and return multiple values.Comments(0) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

**How to create an array formula**

- Select cell B10
- Click in formula bar
- Paste above array formula
- Press and hold CTL + SHIFT
- Press Enter

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.Comments(2) Filed in category: Built-in features, Count values, Excel

**How to copy formula**

- Select cell B10
- Copy cell (Ctrl + c)
- Select cell range B11:B15
- Paste (Ctrl + v)

### Explaining array formula in cell B10

The index function returns a value or a reference of the cell at the intersection of a particular column and row, in a given range.

INDEX($B$2:$E$7, row_num, column_num)

The first following three steps calculate the row_nums and the remaining steps calculate column_nums.

**Calculate row_nums**

**Step 1 - Find matching dates and non blanks**

($A$2:$A$7=$B$9)*($B$2:$E$7<>"")

becomes

({40909; 40910; 40911; 40912; 40913; 40909}=40909)*({"New York","Los Angeles",0,"Chicago"; 0,"Houston",0,0; "Philadelphia","Phoenix","San Antonio",0; "San Diego",0,"Dallas","San Jose"; "Jacksonville",0,0,"Indianapolis"; 0,"Austin",0,"San Francisco"}<>"")

and returns

{1, 1, 0, 1;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 1, 0, 1}

**Step 2 - Return corresponding row numbers**

IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), "")

becomes

IF({1, 1, 0, 1;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 1, 0, 1}, {1;2;3;4;5;6}, "")

and returns

{1, 1, "", 1;"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", "";"", 6, "", 6}

Comments(9) Filed in category: Excel, Functions

**Step 3 - Return the k-th smallest value**

SMALL(array, k)

SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1))

becomes

SMALL({1, 1, "", 1;"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", "";"", 6, "", 6}, 1)

and returns 1.

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.Comments(12) Filed in category: Excel, Functions

**Calculate column_nums**

**Step 1 - Find matching dates and non blanks**

($A$2:$A$7=$B$9)*($B$2:$E$7<>"")

becomes

({40909; 40910; 40911; 40912; 40913; 40909}=40909)*({"New York","Los Angeles",0,"Chicago"; 0,"Houston",0,0; "Philadelphia","Phoenix","San Antonio",0; "San Diego",0,"Dallas","San Jose"; "Jacksonville",0,0,"Indianapolis"; 0,"Austin",0,"San Francisco"}<>"")

and returns

{1, 1, 0, 1;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 1, 0, 1}

**Step 2 - Calculate both row numbers and column numbers**

IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), "")

becomes

IF({1, 1, 0, 1;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 0, 0, 0;0, 1, 0, 1}, {1;2;3;4;5;6}+{1,0.5,0.333333333333333,0.25}, "")

and returns

{2,1.5,"",1.25;"","","","";"","","","";"","","","";"","","","";"",6.5,"",6.25}

**Step 3 - Return the k-th smallest value**

SMALL(array, k)

SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), ""), ROW(A1))

becomes

SMALL({2,1.5,"",1.25;"","","","";"","","","";"","","","";"","","","";"",6.5,"",6.25}, 1)

and returns 1.25.

**Step 4 - Subtract row numbers**

SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), ""), ROW(A1))-SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1))

becomes

SMALL({2,1.5,"",1.25;"","","","";"","","","";"","","","";"","","","";"",6.5,"",6.25}, 1)-SMALL({1, 1, "", 1;"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", "";"", 6, "", 6}, ROW(A1))

becomes

1.25-1

and returns 0.25

**Step 5 - Calculate column number**

1/(SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), ""), ROW(A1))-SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7<>""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1)))

becomes

1/0.25 and returns column number 4.

### Download excel *.xlsx file

Lookup and return multiple values from a range excluding blanks.xlsx

### Category: Vlookup and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Comments(441) Filed in category: Excel, VLOOKUP and return multiple values

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]

Comments(153) Filed in category: Excel, VLOOKUP and return multiple values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

VLOOKUP and return multiple matches based on many criteria.

Comments(38) Filed in category: Excel, VLOOKUP and return multiple values

Lookup with criteria and return records.

Comments(35) Filed in category: Excel, VLOOKUP and return multiple values

Question: I have a list and I want to filter out all rows that has a value (Column C) that […]

Comments(20) Filed in category: Excel, VLOOKUP and return multiple values

Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]

Comments(6) Filed in category: Dates, Excel, VLOOKUP and return multiple values

The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]

Comments(2) Filed in category: Excel, Sort values, VLOOKUP and return multiple values

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]

Comments(1) Filed in category: Excel, Sorted unique distinct values, VLOOKUP and return multiple values

### 14 Responses to “Lookup and return multiple values on the same row from a range excluding blanks”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Let's say that you only want to return the values in Column C (LA and Austin), how would the formula be written to accommodate this request? Also, what if you have numerical values in the cell and do not want blank cells to be included? How do you write the formula in that scenario? Thanks.

KO,

Array formula in cell B11:Download excel *.xlsx file:Lookup-and-return-multiple-values-from-a-range-excluding-blanks-KO.xlsx

What about the other way around? Lookup the location and return the dates?

Marco,

Your lookup location is in cell B16, array formula in cell B17:

=INDEX($A$2:$A$7, SMALL(IF($B$16=$B$2:$E$7, MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1)))

Thanks Oscar! It works that way but i noticed the cities don't repeat in different dates. What if they did? Is it possible to return multiple dates, i.e., what if Los Angeles was in two or more different dates?

I got it! But i still got a doubt. What if in between dates there were blanks and i don't want it to return blanks as well?

Got it was well! Thanks for the help!

=IFERROR(INDEX($B$2:$E$7, SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1)), 1/(SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7))+1/MATCH(COLUMN($B$2:$E$7), COLUMN($B$2:$E$7)), ""), ROW(A1))-SMALL(IF(($A$2:$A$7=$B$9)*($B$2:$E$7""), MATCH(ROW($B$2:$E$7), ROW($B$2:$E$7)), ""), ROW(A1)))), "")

the formula okay but the size is problem, any possibility more efficient formulas?

Thanks

Rizky,

the formula okay but the size is problem, any possibility more efficient formulas?I wish I knew a smaller formula but I don´t. Are you an excel 2003 user?

Nop Im just wonder another formula which more efficient, and my last question, could you tweak the formula so we can retrieve the value based on Column Header? Opposite from the case above which return values from Row Header as criteria....

Thanks

Can you please let me know what changes I need to make in this formula to have all the matching cities are populated in a single cell like

City1

City2

City3

Also I need to have a tooltip on the cell to show the description

his is the data table:

S/N RailCorp Ref Number Date In

77203 HRC mod program 10377 24/05/2011

77204 HRC mod program 10285 20/04/2011

77697 HRC mod program 10489 5/07/2011

77698 HRC mod program 10554 8/08/2011

77699 HRC mod program 10408 8/06/2011

77700 HRC mod program 10553 8/08/2011

77701 HRC mod program 10441 23/06/2011

77702 HRC mod program 10442 23/06/2011

77703 HRC mod program 10318 11/05/2011

77717 HRC mod program 10286 20/04/2011

77718 HRC mod program 10490 5/07/2011

79224 HRC mod program 10409 8/06/2011

79225 HRC mod program 10376 24/05/2011

79226 HRC mod program 10210 17/02/2011

79227 HRC mod program 10317 11/05/2011

I don't want to input the S/N in Table 2 manually. I need a formula for it as this is only part of the data that I need to categorise.

I need the S/N listed by the quarter they came in (Date In).

Yealy Quarter No Of Units S/N

Q1-2011 1

Q2-2011 10

Q3-2011 4

Q4-2011 0

Q1-2012 0

If someone can please help.

this is the result I am after but it should be done using formulas

Yealy Quarter No Of Units S/N

Q1-2011 1 79226

Q2-2011 10 77203, 77204, 77699, 77701, 77702, 77703, 77717, 79224, 79225, 79227

Q3-2011 4 77697, 77698, 77700, 77718

Q4-2011 0 NA

Q1-2012 0 NA

Thnaks in Advance

this is the data table:

S/N RailCorp Ref Number Date In

77203 HRC mod program 10377 24/05/2011

77204 HRC mod program 10285 20/04/2011

77697 HRC mod program 10489 5/07/2011

77698 HRC mod program 10554 8/08/2011

77699 HRC mod program 10408 8/06/2011

77700 HRC mod program 10553 8/08/2011

77701 HRC mod program 10441 23/06/2011

77702 HRC mod program 10442 23/06/2011

77703 HRC mod program 10318 11/05/2011

77717 HRC mod program 10286 20/04/2011

77718 HRC mod program 10490 5/07/2011

79224 HRC mod program 10409 8/06/2011

79225 HRC mod program 10376 24/05/2011

79226 HRC mod program 10210 17/02/2011

79227 HRC mod program 10317 11/05/2011

I don't want to input the S/N in Table 2 manually. I need a formula for it as this is only part of the data that I need to categorise.

I need the S/N listed by the quarter they came in (Date In).

Yealy Quarter No Of Units S/N

Q1-2011 1

Q2-2011 10

Q3-2011 4

Q4-2011 0

Q1-2012 0

If someone can please help.

this is the result I am after but it should be done using formulas

Yealy Quarter No Of Units S/N

Q1-2011 1 79226

Q2-2011 10 77203, 77204, 77699, 77701, 77702, 77703, 77717, 79224, 79225, 79227

Q3-2011 4 77697, 77698, 77700, 77718

Q4-2011 0 NA

Q1-2012 0 NA

Thnaks in Advance

Thanks a lot for this example. This is great!

I know this sounds a bit stupid, but..

The formula is printing cities going from E2 to B2 and then down a row.

What i need is to print them per column, going from B2 to B7, then C2 to C7, and so on.

Could you help??

I am banging my head on the wall :|