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

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

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Get Excel *.xlsx file

vlookup of three columns to pull a single record.xlsx

How to use VLOOKUP with multiple conditions

I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]

How to return a value if lookup value is in a range

In this article, I will demonstrate four different formulas that allow youÂ to lookup a value that is to be found […]

Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]

Return multiple values if in range

The image above shows a formula in cell C11 that extracts values from column D if the number in cell […]

How to use the VLOOKUP function

The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]

Choose between two data sets to VLOOKUP

Question: How doÂ I search a specific data set, I have two tables to choose from? Answer: Formula in cell C13: […]

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

### Leave a Reply to Mike

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

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.. :-)