## Lookup multiple values in different columns and return a single value

**Table of contents**

- Lookup multiple values in different columns and return a single value
- Lookup multiple values in different columns and return multiple values

### Lookup multiple values in different columns and return a single value

I m working on the below table.

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

I need the status column to be vlooked up on another file by comparing all the remaining 4 columns.(the sheet to be updated carries the 4 columns not in the same order as in the original sheet.. its mixed).. Pls help me with dis..

Thanks

S.Babu

### Answer:

**Sheet1**

**Sheet2**

**Array formula in cell E2, sheet2:**

**How to create an array formula**

- Select cell E2
- Paste array formula
- Press and hold Ctrl + Shift
- Press Enter

**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**

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

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)

becomes

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)

and returns {0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}

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

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.

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

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.

**Download excel *.xlsx file**

### Lookup multiple values in different columns and return multiple values

I have a set of data, like the one you used in the original example that also has a column for the date of the transaction. I would like my Index-type formula to search for both the main item (the rep's name) and also if the date of the transaction falls in the date range).

Start Date: 11/26/2012

End Date: 11/30/2012 (both entered by the user)

Rep: John

Then the results, in each row/column of the 'result' section (INDEX formula results) would show results for John that occurred from 11/26 to 11/30 (including both dates).

Thanks for any help with the formula for that.

Jason

**Answer:**

**Array formula in cell B18:**

**How to create an array formula**

See steps above!

**How to copy an array fomula**

See steps above!

### Explaining array formula in cell B18

**Step 1 - Compare value in cell B15 to cell range $B$2:$B$11**

$B$15=$B$2:$B$11

becomes

"John"={"John";"Jennifer";"Laura";"Paul";"John";"Laura";"Jennifer";"Paul";"Paul";"John"}

and returns

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}

**Step 2 - Compare start and end date to date column**

($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13)

becomes

({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE})*({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE})

and returns

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

**Step 3 - If a record is a match return it´s row number**

IF(($B$15=$B$2:$B$11)*($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13),MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11))

becomes

IF({TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}*{1;1;1;1;1;1;1;0;0;0},MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11))

becomes

IF({1;0;0;0;1;0;0;0;0;0},{1; 2; 3; 4; 5; 6; 7; 8; 9; 10})

and returns

{1;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;FALSE;FALSE}

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

SMALL(IF(($B$15=$B$2:$B$11)*($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13),MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)),""),ROW(A1))

becomes

SMALL({1; FALSE; FALSE; FALSE; 5; FALSE; FALSE; FALSE; FALSE; FALSE},ROW(A1))

becomes

SMALL({1; FALSE; FALSE; FALSE; 5; FALSE; FALSE; FALSE; FALSE; FALSE},1)

and returns 1

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

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

becomes

INDEX($C$2:$C$11,1)

becomes

INDEX({"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"; "I"; "J"},1)

and returns A in cell B18

**Download excel *.xlsx file**

Lookup multiple values in different columns and return multiple values.xlsx

### 30 Responses to “Lookup multiple values in different columns and return a single value”

### Leave a Reply

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

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

your 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

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