## Lookup multiple values across columns and return a single value

This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns.

ORDER MODEL MATERIAL QTY STATUS

BOM a s6 1 COMPLETED

BOM b c6 2 NOT COMPLETED

BOM c s6 1 COMPLETED

DEL d c6 3 NOT COMPLETED

EXP a a8 4 IN PROGRESS

DEL b d2 5 COMPLETED

DEL c c6 4 NOT COMPLETED

DEL d s6 7 NOT COMPLETED

DEL e c6 8 NOT COMPLETED

DEL r a8 1 COMPLETED

EXP g d1 5 COMPLETED

EXP r c6 9 COMPLETED

EXP t a8 2 COMPLETED

EXP a c6 1 NOT COMPLETED

EXP b s6 9 COMPLETED

EXP c c6 1 NOT COMPLETED

EXP d a8 4 NOT COMPLETED

S.Babu

#### Sheet1 - Data

#### Sheet2 - Criteria and result

The following array formula uses the corresponding values in column A, B, C and D to do a lookup in Sheet1 and return a value in column E.

Array formula in cell E2, sheet2:

### Watch a video explaining formula above

Recommended article:

Lookup multiple values in different columns and return multiple values

Jason C asks: I have a set of data, like the one you used in the original example that also […]

Lookup multiple values in different columns and return multiple values

The following article demonstrates how to do a lookup and return a sorted list:

Use VLOOKUP and return multiple values sorted from A to Z

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

Use VLOOKUP and return multiple values sorted from A to Z

**How to create an array formula**

- Copy above array formula
- Double click on cell E2
- Paste array formula
- Press and hold Ctrl + Shift simultaneously
- Press Enter once
- Release all keys

Array formulas allows you to do advanced calculations not possible with regular formulas.

**How to copy array formula**

- Select cell E2
- Copy cell (Ctrl + c)
- Select cell E3:E20
- Paste (Ctrl + v)

**Explaining array formula in cell E2**

I recommend that you use the "Evaluate Formula" feature in Excel to troubleshoot or to simply understand how a formula works.

Select the cell containing the formula you want to evaluate, go to tab "Formulas" on the ribbon. Click on "Evaluate Formula" button to start evaluating.

A dialog box appears, click the "Evaluate" button to go through the formula calculations step by step.

*Step 1 - Count the number of cells by a given set of criteria*

*Step 1 - Count the number of cells by a given set of criteria*

The COUNTIFS function can work with up to 127 argument pairs:

COUNTIFS(*criteria_range1*, *criteria1*, [*criteria_range2*, *criteria2*]…)

There are four criteria and the COUNTIFS function requires eight arguments, in order to get an array of values that we can use the second argument in each pair is a cell range.

You are probably not used to this setup but it works fine, the array allows you to identify where the match is or in other words where all criteria match.

COUNTIFS(Sheet2!A2, Sheet1!$D$2:$D$18, Sheet2!B2, Sheet1!$B$2:$B$18, Sheet2!C2, Sheet1!$C$2:$C$18, Sheet2!D2, Sheet1!$A$2:$A$18)

returns the following array {0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}.

This array tells us that the match is in row 3 because 1 is in the third position in the array. Note that all criteria must match in order to return 1.

*Step 2 - Return the relative position of an item in an array that matches a specified value*

*Step 2 - Return the relative position of an item in an array that matches a specified value*

The MATCH function returns the relative position of a value in a cell range or array.

MATCH(1, COUNTIFS(Sheet2!A2, Sheet1!$D$2:$D$18, Sheet2!B2, Sheet1!$B$2:$B$18, Sheet2!C2, Sheet1!$C$2:$C$18, Sheet2!D2, Sheet1!$A$2:$A$18), 0)

becomes

MATCH(1, {0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, 0)

and returns 3. Value 1 is found in the third position in the array.

*Step 3 - Return the value of a cell at the intersection of a particular row and column*

*Step 3 - Return the value of a cell at the intersection of a particular row and column*

The INDEX function returns a value based on a row and column number, there is only a row number in this case so you can omit the column argument.

INDEX(*cell_reference*, *[row_num]*, *[column_num]*)

The first argument is the cell reference from which you want to get a specific value from.

INDEX(Sheet1!$E$2:$E$18, MATCH(1, COUNTIFS(Sheet2!A2, Sheet1!$D$2:$D$18, Sheet2!B2, Sheet1!$B$2:$B$18, Sheet2!C2, Sheet1!$C$2:$C$18, Sheet2!D2, Sheet1!$A$2:$A$18), 0))

becomes

INDEX(Sheet1!$E$2:$E$18, 3)

and returns "COMPLETED" in cell E2.

Filter shared records from two tables

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

Filter unique distinct records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

Prevent duplicate records in a worksheet

This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to […]

How to use the COUNTIFS function

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

INDEX MATCH – multiple results

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]

INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first […]

INDEX and MATCH – multiple criteria and multiple results

The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied […]

The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]

### 31 Responses to “Lookup multiple values across columns and return a single value”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

ENTER IPC AET010AX

ACCT TYPE

Z.INP #N/A

Z.INPCDRHB #N/A

Z.INPCDTOX #N/A

Z.INPCDU #N/A

I want to be able to enter an IPC and have it come back with responsibility based on IPC and Acct type from the following table.

IPC SYSTEM ACCT TYPE RESPONSIBILITY

AET010AX CAMIS Z.INP Aetna

AET015AX CAMIS Z.INP Aetna Senior

AET015CC CAMIS Z.INP AETNA

BLC010AX CAMIS Z.INP Blue Cross

BLC015AX CAMIS Z.INP Blue Cross Senior

BLC015CC CAMIS Z.INP BLUE CROSS

BLC400CC CAMIS Z.INP BLUE CROSS

BLS010AX CAMIS Z.INP Blue Shield

I was using index with match with no luck. HELP

Candy,

Sheet2

Sheet1

Array formula in cell B4:

see attached file:

Candy.xlsx

i need to extract the headers from a grid based on value in left most column

example

row header ---> a b c d e

data 1 1 2 2 2

2 1 1

1 1 1 2

so how to find out which all headers appear agst 1 or 2o 3 in each row

vikas,

Read this post:

Match a criterion and extract multiple corresponding table headers

I have a worksheet(#1) and I want to populate the amount Column with data in another worksheet in the same workbook, Based on 2 criteria: State and Date

Worksheet #2 Table A1:D19 = EST

ColA ColB ColC ColD

1 AK 5/31 1st Installment 53,397.00

2 AK 8/31 2nd Installment 53,397.00

3 AK 11/30 3rd Installment -

4 AL 5/15 1st Installment -

5 AL 8/15 2nd Installment 53,349.00

6 AL 11/15 3rd Installment -

7 AR 5/15 1st Installment 33,237.00

8 AR 8/15 2nd Installment 33,237.18

9 AR 11/15 3rd Installment -

10 AZ 3/15 1st Installment 62,738.89

11 AZ 4/15 2nd Installment 62,738.89

12 AZ 5/15 3rd Installment 62,738.89

13 AZ 6/15 4th Installment 137,986.05

14 AZ 7/15 5th Installment 81,550.68

15 AZ 8/15 6th Installment 81,550.68

16 CA 4/1 1st Installment 495,364.00

17 CA 6/1 2nd Installment 495,364.00

18 CA 9/1 3rd Installment 495,364.00

19 CA 12/1 4th Installment -

Worksheet #1 I need the Worksheet 2 ColD amount to be put in the corresponding State and Date ColE on this worksheet. Can you help?

ColA ColB ColC ColD ColE

1 AK 5/31 -

2 AK 8/31 -

3 AK 11/30 -

4 AL 5/15 -

5 AL 8/15 -

6 AL 11/15 -

7 AZ 3/15 -

8 AZ 4/15 -

9 AZ 5/15 -

10 AZ 6/15 -

11 AZ 7/15 -

12 AZ 8/15 -

13 AR 5/15 -

14 AR 8/14 -

15 AR 11/14 -

16 CA 4/1 -

17 CA 6/1 -

18 CA 9/1 -

19 CA 12/1 -

Phoneix ,

read this: Fetch data from another table

[...] shift enter NOT just enter, they can then be copied down. You may find some useful tips here.... Lookup multiple values in different columns and return a single value | Get Digital Help - Microsoft... Explaining a formula: Lookup values in a range using two or more criteria and return multiple [...]

VBA Code ?

Please help me with this I Need to look up several values and in the return the answer should be one.

Quantity On Hand Safety Stock Minimum Date

3/5/2013 7/5/2013 13/05/2013 21/05/2013 27/05/2013 30/05/2013

1.77 1.77 1.69 1.655 1.655 1.655 0.44

0 0 0 0 0 0 0

0 0 0 0 0 0 0

29.36 29.36 29.36 28.749 26.739 26.739 10

2.22 2.22 1.824 0.274 0.272 0.272 4.44

456.12 456.12 446.617 1086.769 1025.432 1025.432 850

8.766 8.766 8.029 8.029 8.029 8.029 15

Am,

Read this post:

Excel udf: Lookup and return multiple values concatenated into one cell

Hi Oscar,

I'm trying to utilize the formulas you've provided, but I'm unable to make them work with my data.

Essentially, I need Excel to bring back the value column C if the values match between column A and column D and column A-1 and column D-1.

A A-1 C D D-1 Column C Value

11:14 2037234 27600V 30 2000640

12:01 2016660 NL1000 "10" 2001931

30 2000640 060904 20,000 Years 2013724

42 2062442 201539 2001 2010036

187 2003556 097605 2010 2012514

300 2035604 200552 24 Hours to Kill 2001391

"10" 2001931 079813 36 Hours 2009741

Thanks in advance!

brett,

I am not sure which values you are looking for?

Blue values, array formula:

=INDEX($C$2:$C$8, SMALL(IF(COUNTIFS($A$2:$A$8, $D$2:$D$8, $B$2:$B$8,$E$2:$E$8)>0, MATCH(ROW($A$2:$A$8), ROW($A$2:$A$8)), ""), ROW(A1)),0)

Red values, array formula:

=INDEX($C$2:$C$8, SMALL(IF(COUNTIFS($D$2:$D$8,$A$2:$A$8, $E$2:$E$8,$B$2:$B$8)>0, MATCH(ROW($A$2:$A$8), ROW($A$2:$A$8)), ""), ROW(A1)), 0)

Download excel file:

Lookup-values-in-different-columns.xlsx

Thanks for looking into this Oscar! I'm looking to bring back values in column C only if there is a match in column A with column D, and a match in column B with column E.

I ended up figuring it out using this (array) formula:

INDEX($C$2:$C$6757,MATCH(1,IF($A$2:$A$6757=D2,IF($B$2:$B$6757=E2,1)),0))

As always, appreciate the help!

brett,

thank you for posting your solution.

Oscar, I thought, I was onto my solution with this but could not get it to play. So here's my problem. I'm trying to create a calendar tool, on sheet 1, I have column A with a list of days from jan-1 to dec-31. I have 10 users with assorted tasks on sheet 2. I am looking for a solution that will match the (Sheet 1)date row and the sheet 1 username from a drop down to all the matching row data on sheet 2. End result Example something like: sheet 1 populates the row "Dec 5 2013" with data from sheet 2 /B col user/ "Bob" /C col date/ Dec 5 2013 /f col task/ "Get Sleigh ready"/ z col/... and then.. Example: sheet 1 populates the row "Dec 12 2013" with sheet 2 /b user/ "Bob" /c col date/ Dec 5 2013 / f col task/ "Add rockets to Sleigh"/ z col/...

I'm sure for the wizard this is simple, but I'm out of ideas. Thank you kind sir!

Oscar,

INDEX('Tasks'!$F$6:$F$100,MATCH($A18,'Tasks'!$L$6:$L$29,0)) will work but I must delete all the other users from the second sheet. Or only works for one user. I would like to select the user name from a dropdown as a filter then apply the above index. Clueless.

thanks

Rae,

Oscar, I thought, I was onto my solution with this but could not get it to play. So here's my problem. I'm trying to create a calendar tool, on sheet 1, I have column A with a list of days from jan-1 to dec-31. I have 10 users with assorted tasks on sheet 2. I am looking for a solution that will match the (Sheet 1)date row and the sheet 1 username from a drop down to all the matching row data on sheet 2. End result Example something like: sheet 1 populates the row "Dec 5 2013" with data from sheet 2 /B col user/ "Bob" /C col date/ Dec 5 2013 /f col task/ "Get Sleigh ready"/ z col/... and then..I understand this.

and then.. Example: sheet 1 populates the row "Dec 12 2013" with sheet 2 /b user/ "Bob" /c col date/ Dec 5 2013 / f col task/ "Add rockets to Sleigh"/ z col/...

I'm sure for the wizard this is simple, but I'm out of ideas. Thank you kind sir!

But not this? Why is row "Dec 12 2013" populated with values from date "Dec 5 2013"?

OK, Fresh look, that was careless, s/b same date. Basically at the top of sheet 1 column A is a dropdown of all my users below that has dates from jan1 to dec 31. In column b is the magical formula that matches the results of the dropdown "user" to the date in column A with like data on sheet 2.

sheet 1 Column C & D would be grabbing more of the row data from sheet 2 like Task, state, etc.

Does this make sense?

Rae,

Please upload an example file and I´ll see what I can do.

hi Oscar

in your response to Jason above under "Lookup multiple values in different columns and return multiple values" - i am also interested in returning and displaying a second column... so not only the Product A and E, but also the Product Price...

thus the results displayed should be:

Column B | Column C

A | $20

E | $10

I would like my customer to make a decision not just based on product but also on the price...

MR.OSCAR

THANKS FOR PROVIDING FOLLOWING FORMULA,BUT AFTER TWO VALUES FETCHED FOLLOWING CELL SHOWING NUM ERROR

=INDEX($C$2:$C$11, SMALL(IF(($B$15=$B$2:$B$11)*($A$2:$A$11=$B$13), MATCH(ROW($A$2:$A$11), ROW($A$2:$A$11)), ""), ROW(A1)))

IN ABOVE FORMULA IF I PUT A3 IN END OF FORMULA IT SHOWS NUM! I DO NOT WANT THIS PLEASE HELP ME IN THIS CASE,IF VALUE NOT MATCH TO FETCH THEN IT SHOULD BE SHOWN EMPTY CELL PLEASE DO THE NEEDFUL.

THANK YOU

REGARDS

SENTHILKUMAR

I have a tale as follows

date time open high low close

2-9 9:01:02 7500 7625 7500 7600

2-9 9:02:02 7600 7650 7600 7600

2-9 9:10:11 7600 7600 7550 7575

2-9 9:12:02 7575 7610 7530 7600

3-9 9:01:15 7600 7700 7600 7668

3-9 9:15:02 7668 7760 7500 7600

3-9 9:19:02 7600 7700 7500 7600

I need to look up a range for data to be arranged for a particular time range for a selected date. in the same format. how do I look up the 1st matching value as open for the time range of that date and also the high low between that date and time range and also the close

have a table as follows

date time open high low close

2-9 9:01:02 7500 7625 7500 7600

2-9 9:02:02 7600 7650 7600 7600

2-9 9:10:11 7600 7600 7550 7575

2-9 9:12:02 7575 7610 7530 7600

3-9 9:01:15 7600 7700 7600 7668

3-9 9:15:02 7668 7760 7500 7600

3-9 9:19:02 7600 7700 7500 7600

I need to look up a range for data to be arranged for a particular time range for a selected date. in the same format. how do I look up the 1st matching value as open for the time range of that date and also the high low between that date and time range and also the close

Hi Oscar,

I have sheet 1 and sheet 2. there r 4 columns in sheet 1 contains some values and sheet 2 having only one column. values are same in both sheet foe column a i want to extract the rest column details to sheet 2 from sheet 1. how can I ???

Hello Oscar,

I have 2 sheets, one that will have FP with different values and FO with different values. 2nd sheet will have the values to match to FP and FO. The problem that I am having is basically if value 1 and value 2 in FO, plus value 1 and value 2 in FP will result in the combination of those.

So, on sheet 2 is the array of values, example would be if on sheet 1, in the answer column, it would factor in the values of column A and column B based on the mapping in sheet 2.

Example, sheet 1, C1, Pipe, or sheet 1, C2,=Fla or sheet 1, C4,=Swi.

It can be set-up another way, but looking to take the value of two cell, if they match the mapping structure, then return the value on the far right. Thanks

Sheet 1

FP FO Answer

1 4

3 2

2 2

2 4

2 1

5 4

5 1

3 4

4 4

4 2

1 2

4 1

2 3

3 1

5 2

1 1

Sheet 2

FP1 FP2 FO1 FO2 Answer

1 2 1 3 Comm

3 4 1 2 Fla

1 2 2 Swi

3 4 4 Swi

Pipe

I have 30 questions in sheet2 and 20 questions in sheet3. so I want to get 10 question from sheet2 and 5 questions from sheet3 randomly. I want to question on sheet1 randomly. How can I get vba excell code it

hi Oscar

I have two columns(C1) and (C2) of integer values in Sheet1. I need a macro to search the values (c1) and (C2) and add it to sheet 2 (A1). The thing here is the columns (c1) and (c2) in sheet1 might exceed sometimes. So the macro should search the whole document sometimes. Please help

hi Oscar

I have two columns(C1) and (C2) of integer values in Sheet1. I need a macro to search the values (c1) and (C2) and add it to sheet 2 (A1). The thing here is the columns (c1) and (c2) in sheet1 might exceed sometimes. So the macro should search the whole document sometimes. Please help

Fab

Hi Oscar,

Please help.

1 6 A

2 5 B

3 4 C

4 3 D

5 2 E

6 1 F

i want my every cell of first column to search its value in column 2 and if it finds the value.....then it should copy the corresponding value of column 3 in column four corresponding to column1.

I means we have first column first cell as 1 and if it search it in column 2 it finds it in cell 6 and corresponding value to cell 6 is F....now it should paste this value F in column 4 but corresponding to column 1 cell 1

Hi Oscar, I need your help to solve my problem.. I have 2 columns in excel both with have values. If any one of the cell OR both cell contains "0" I need the output to be TRUE if not FALSE

VALUE1 VALUE2 OUTPUT

1234 7894 False

0 1234 TRUE

4567 0 TRUE

0 0 TRUE

-4567 0 TRUE

0 -984 TRUE

-7456 7456 FALSE

Thanks

Hii oscar

Help me to sort out it

I have

A1(anil singh raj)

It can be anything Like

A1(singh raj anil)

I want return value in

B1 (10 30 20)

Or

B1(30 20 10)

Or

Lookup array is

D1(anil) E1(10)

D2(raj) E2(20)

D3(singh) E3(30)

Kindly help me to do it?