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

### 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: http://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.. :-)