## Vlookup of three columns to pull a single record

**Question:** Does anyone know how to do a vlookup of three columns to pull a single record?

**Answer:** I am guessing that you want to lookup a value in any of three columns to pull a single record. In this example I search column 1,2 and 4. The search result is pulled out of column 5.

**Array formula in B17 using named ranges:**

**1**, 1))+COUNTIF(search_values, INDEX(tbl, ,

**2**, 1))+COUNTIF(search_values, INDEX(tbl, ,

**4**, 1)), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROW(A1)),

**5**) + CTRL + SHIFT + ENTER

copied down as far as needed.

**Array formula in B17 using cell references:**

**5**) + CTRL + SHIFT + ENTER

copied down as far as needed.

The bolded numbers in the above formulas are column numbers. See column numbers in the picture above.

Above formulas can also pull multiple records.

### Named ranges

tbl ($C$5:$H$10)

search_values (B13)

What is named ranges?

### How to increase the number of search strings

Change the named range search_values.

### Download excel example file

vlookup of three columns to pull a single record.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

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

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROW(**reference**)** returns the rownumber of a reference

### Category: Records

Match two criteria and return multiple rows in excel

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price a […]Comments(143) Filed in category: Excel, Records

Compare two lists of data: Filter common row records in excel

This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: […]Comments(7) Filed in category: Compare, Excel, Records

Compare two tables in excel: Remove common rows

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]Comments(5) Filed in category: Compare, Records

Quickly highlight records in a list using multiple criteria in excel

This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in […]Comments(3) Filed in category: Conditional formatting, Excel, Records

### Category: Vlookup

How to return multiple values using vlookup in excel

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

Array formula to look up a value 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

Comments(74) Filed in category: Excel, Vlookup

Vlookup – Return multiple unique distinct values in excel

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 with 2 or more lookup criteria and return multiple matches in excel

VLOOKUP and return multiple matches based on many criteria.Comments(38) Filed in category: Excel, VLOOKUP and return multiple values

### 10 Responses to “Vlookup of three columns to pull a single record”

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

Hi Oscar,

How do i change the font size and color in a combo box ?

Appreciate your help.

Thanks

Haroun

Haroun,

You can only change font size and color in an active x combo box.

Read more: https://www.ozgrid.com/forum/showthread.php?t=73189

hi oscar,

this above solution is very good and very handy. thank you very much. i made a slight update to this for error-suppression that i thought of sharing here:

={LOOKUP(REPT("Z",25), CHOOSE({1,2},"", INDEX(tbl, SMALL(IF(COUNTIF(search_values, INDEX(tbl, , 1, 1))+COUNTIF(search_values, INDEX(tbl, , 2, 1))+COUNTIF(search_values, INDEX(tbl, , 4, 1)), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROW(A1)), 5)))}

i do not have access to XL'03 in order to check this, but i hope that by _not_ using XL'07-specific "IFERROR" for error-suppression, this formula may be useful for older versions too. am i correct in that assumption?

the purpose for which i employed this formula, i was able to drop the 'area_num' argument from INDEX. is there a specific situation in which *not* having that would wreak havoc?

as always, much thanks and kind regards for all that you share with us.

K. Yantri

i do not have access to XL'03 in order to check this, but i hope that by _not_ using XL'07-specific "IFERROR" for error-suppression, this formula may be useful for older versions too. am i correct in that assumption?No, use =IF(ISERROR(formula), errorformula, formula)

Thanks for commenting!

Does anyone know how to match four columns to pull a single record?

Sheet 1

Description Age Sum of PWK01

Fred A =value reqquired

Mike B =value reqquired

Samuel C =value reqquired

Joshua D =value reqquired

Eric E =value reqquired

Sheet 2

Description Item Age Week 1 Week 2

Fred Kiwis A 31.802571712 37.802571712

Mike Kiwis D 20.528476326 21.528476326

Samuel Kiwis C 52.331048038 51.331048038

Joshua Kiwis F 1457907.9884 1467907.9884

Eric Kiwis E 1481550.2918 1491550.2918

Fred Kiwis B 31.802571712 37.802571712

Mike Kiwis B 20.528476326 21.528476326

Samuel Kiwis G 52.331048038 51.331048038

Joshua Kiwis D 1457907.9884 1467907.9884

Eric Kiwis I 1481550.2918 1491550.2918

Thanks Mike

Mike,

I think I can do that. But I don´t understand your data. What is the desired outcome?

Dear Oscar Sir,

I am searching for one tricky thing to accomplish using (only) formulas (and not VBA).

I will be thankful if you can help me.

The excel sheet has several columns, I want to filter data by two columns, here, column Speciality = "*Port*", and also, Testing? = "No", now the answer should be value of column "Name" for the first resulting row from the filter formula.

Excel preview data is as follows:

-------------------

Name Speciality Perma? Testng? Success?

A Oil Engine & Automobiles No Yes Yes

B Diamond & Textile Industries No Yes No

C Plastic Industries & Wine No Yes Yes

D IT & Automation No Yes Yes

E Brass Material & Port No Yes Yes

F Port & Shipping Industries No No N/A

G Tours & Spices No Yes Yes

H General No Yes No

I Tours, Divine, Port, etc No No N/A

J Tours & Fisheries No Yes Yes

K Tours & Others No Yes Yes

L Tours & Others Yes Yes Yes

M Film Industries & Hotels Yes Yes No

N Plastic & Other Industries No Yes Yes

O Tours, Wine & Port Yes Yes Yes

Name of person who has speciality matching "PORT" and is not in "Testing" version:

ANSWER = ?? FORMULA ??

Speciality = "*PORT*" + Testing? = "No"

=

[Respective Value of: Column A]

-------------------

In this case, answer should be: F

Awaiting for your reply.

Thanks & Regards,

Deep

Deep,

Array formula in cell A21:

=INDEX($A$2:$A$16, MATCH(1, ISNUMBER(SEARCH(A19, $B$2:$B$16))*($C$2:$C$16=B19), 0))

The answer should be E?

Thanks for the code. I'll check it out. (Sorry for delayed response)

:) Keep up the good work..

Yes sir!! Perfect answer.

Wow! Amazing.. 10 out of 10.. :-)