Solved

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

Posted on 2015-02-11
16
134 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
  • 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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 …
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now