Solved

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

Posted on 2015-02-11
16
154 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Part One of the two-part Q&A series with MalwareTech.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

622 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