Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

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

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
VB6chuck
Asked:
VB6chuck
  • 9
  • 7
1 Solution
 
OnisanCommented:
The Exact Syntax you need is
=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"COMTYPE")>0,"COMTYPE","")
0
 
OnisanCommented:
This can go anywhere on Sheet1
0
 
OnisanCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
VB6chuckAuthor Commented:
"COMTYPE" should not be returned.  The value on the sample in red is correct for that location.
0
 
VB6chuckAuthor Commented:
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
 
OnisanCommented:
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
 
OnisanCommented:
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
 
OnisanCommented:
Here is your file with a working set of Array Formulas
EXCEL-Question-re-VLOOKUP.xlsx
0
 
VB6chuckAuthor Commented:
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
 
OnisanCommented:
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
 
OnisanCommented:
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
 
VB6chuckAuthor Commented:
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
 
VB6chuckAuthor Commented:
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
 
VB6chuckAuthor Commented:
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
 
OnisanCommented:
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
 
VB6chuckAuthor Commented:
I submitted a comment before accepting the solution.  Where has that carefully worded comment gone?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now