Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

I need Excel 0210 VLOOKUP request for multiple sheets and multiple criteria

Posted on 2015-02-11
16
Medium Priority
?
162 Views
Last Modified: 2015-02-12
I would like the exact VLOOKUP syntax to accomplish as per the attached Excel file.  This file has data on Sheet 1 and Sheet 2.  On sheet 1 there is a cell in red - and a description below in red for what I need to do.  Please be sure that the syntax provided includes the reference to the Sheet.

Thank you.
EXCEL-Question-re-VLOOKUP.xlsx
0
Comment
Question by:VB6chuck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 3

Expert Comment

by:Onisan
ID: 40603839
The Exact Syntax you need is
=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"COMTYPE")>0,"COMTYPE","")
0
 
LVL 3

Expert Comment

by:Onisan
ID: 40603843
This can go anywhere on Sheet1
0
 
LVL 3

Expert Comment

by:Onisan
ID: 40603860
And if you want it to return COMTYPE only where Contrace = A2 and Value = B2 And OPTFIELD = "COMTYPE" THEN The Syntax to put the formula on any sheet anywhere is

=IF(COUNTIFS(Sheet2!A:A,Sheet1!A2,Sheet2!C:C,Sheet1!B2,Sheet2!B:B,"COMTYPE")>0,"COMTYPE","")
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:VB6chuck
ID: 40603865
"COMTYPE" should not be returned.  The value on the sample in red is correct for that location.
0
 

Author Comment

by:VB6chuck
ID: 40603882
Thank you Onisan

=INDEX(Sheet2!C2:C16,MATCH(Sheet2!A2:A16,IF(Sheet2!B2:B16="COMTYPE",$A2:$A6),0))
gives a correct solution for B2 on sheet 1, but is incorrect when copied down to rows B3-B6 on sheet 1.

Please let me know how can this be fixed?
0
 
LVL 3

Expert Comment

by:Onisan
ID: 40603970
OK So in Sheet1 Cell B2 Put the following Formula

=INDEX(Sheet2!$C:$C,MATCH(1,(Sheet2!$A:$A=Sheet2!$A2)*(Sheet2!$B:$B="COMTYPE"),0),1)

Then we need to make it an Array formula before copying it so whilst in the formula Ctl Shift and Enter
this puts {} around the formula

No copy down
0
 
LVL 3

Expert Comment

by:Onisan
ID: 40603992
I am so sorrym the Match should say Sheet!
so

=INDEX(Sheet2!$C:$C,MATCH(1,(Sheet2!$A:$A=Sheet1!$A2)*(Sheet2!$B:$B="COMTYPE"),0),1)
0
 
LVL 3

Expert Comment

by:Onisan
ID: 40604006
Here is your file with a working set of Array Formulas
EXCEL-Question-re-VLOOKUP.xlsx
0
 

Author Comment

by:VB6chuck
ID: 40604018
Thank you Onisan.

It now works ok in Sheet1, Cell B2 only, we agree on the no copy down.  Sadly, the copy down is urgently needed as we have somewhere around 1200 rows to do at present.  What can you do to help us on copy down?
0
 
LVL 3

Expert Comment

by:Onisan
ID: 40604045
It should work on Copy Down, that's what I did for the file I attached, It's a big formula so recalculating takes time
Watch the bottomRight corner to see it calculatiing
0
 
LVL 3

Expert Comment

by:Onisan
ID: 40604052
You have left it as an Array Formula (Ctl+Shift+Enter) on the first cell (B2) and then just used Drag to copy the formula down
0
 

Author Comment

by:VB6chuck
ID: 40604085
Have a peculiar situation now.  B2, B3, B4, B6 are  working using VLOOKUP syntax as your 13:55 version above.
B5 returns #NA
However B5 as well as A5 have GREEN triangles at top left of the cell.
How do I get rid of these?
Once the formula is working for column B I need to copy it to column C (etc) and change "COMTYPE" to "DOCKETNO".  This does not work in any version thus far.

We seem to be getting there.
0
 

Author Comment

by:VB6chuck
ID: 40604099
We're wondering if INDEX or COUNTIF are the best way to go to get a general purpose result, or if nested VLOOKUPs with an IF statement is better?
0
 

Author Comment

by:VB6chuck
ID: 40604173
Onisan,

We would be glad to resume the fine tuning tomorrow after 1 PM eastern, if any open issues remain.  We are otherwise busy for the remainder of today, and thank you for continuing to help with this.
0
 
LVL 3

Accepted Solution

by:
Onisan earned 2000 total points
ID: 40605046
Please download the latest version of Excel that I have posted here. It shows a complete working example for all columns .

When you change the formula (I've done this for you in the example) for columns C and D you CTL + SHIFT + ENTER to make sure the formula is an ARRAY Formula.
You can then copy the formulas down the page by highlighting B2:D2 and going to the bottom left corner of D2 and when it turns into a Black CrossHair, Left Mouse Button Down , Hold it down and drag the formulas down to D6

Because these are ARRAY Formulas, the calculation of the sheet will take a couple of seconds so just wait a few as the cell values change.

The Green triangles you are seeing is because a cell is not formatted as expected and isn't formatted as per the surrounding cells. I have formatted both the Range in Sheet1!A1:D6 and Sheet2!A1:C16 as Text so that the formulas work.

I have attached your File with formulas in all cells exactly as you have requested.
There are any ways to achieve the same result using Nested If Statements but this way uses only the two functions INDEX and MATCH and uses them as they were designed to be used.

As an Alternative I have inserted a third sheet Named TwoWayLookUp showing you how to achieve the same thing without INDEX OR MATCH

You can test either sheet by deleting Ranges B3:D6 and then dragging the formulas down as described above.


I am sure you will agree, I have now answered your question as requested.
EXCEL-Question-re-VLOOKUP.xlsx
0
 

Author Comment

by:VB6chuck
ID: 40606654
I submitted a comment before accepting the solution.  Where has that carefully worded comment gone?
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question