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

1. Haroun says:

   April 3, 2012 at 8:36 pm

   Hi Oscar,

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

   Appreciate your help.

   Thanks
   Haroun

   Reply
2. Oscar says:

   April 9, 2012 at 11:34 am

   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

   Reply
3. K. Yantri says:

   April 26, 2012 at 5:34 am

   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.

   Reply
4. Oscar says:

   April 30, 2012 at 12:52 pm

   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!

   Reply
5. Mike says:

   June 26, 2013 at 2:37 pm

   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

   Reply
   • Oscar says:

     July 2, 2013 at 8:43 am

     Mike,

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

6. Deep says:

   April 15, 2014 at 9:23 pm

   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

   Reply
   • Oscar says:

     April 23, 2014 at 11:54 am

     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?

   • Deep says:

     May 5, 2014 at 11:16 am

     Thanks for the code. I'll check it out. (Sorry for delayed response)
     :) Keep up the good work..

   • Deep says:

     May 5, 2014 at 11:22 am

     Yes sir!! Perfect answer.
     Wow! Amazing.. 10 out of 10.. :-)

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 &lt; and > becomes &gt;

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