## VLOOKUP of three columns to pull a single record

*Article updated on February 19, 2018*

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

### Download Excel *.xlsx file

vlookup of three columns to pull a single record.xlsx

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

Remember to convert less than and larger than signs to html character entities before you post your comment.

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