CONDITIONAL VLOOKUP MULTIPLE VALUES, MULTIPLE SHEETS - VLOOKUP-INDEX? INDEX-MATCH?

[jpd126]

Hello,

I recently linked a workbook with multiple worksheets to the SQL database of our financial system. I am trying to now make the thousands of lines in each of these tables more end-user friendly. In many of these tables there a duplicate fields that I want to use to link to a master sheet, but I am having trouble developing the proper conditional equation to look at two sheets, two different fields and if the match populate a third field. I have tried Index, Match and vlookup but with not luck. I think once I get assistance with this formula will be be in great shop to work with the rest of the data. I am just stuck with this formula to start. I am uploading two simplified sheets for review and below is an explanation of what I am trying to do. I am new to the message board and appreciate any assistance. Happy New Year!

img_ES1: This sheet contains the master data.
img_PS1: This sheets contains data that will help give clarify to the master data sheet.

I am trying to have the formula look at prep_proj and prep_pay columns in sheet ES1 and compare it to prpt_proj and prpt_code sheet PS1. If they both match then I want it to put prpt_long from sheet PS1 into "PR_PAY_DESCRIPTION" in sheet ES1.

Thank you for any assistance.

 

[datatronics505]

I am trying to have the formula look at prep_proj and prep_pay columns in sheet ES1 and compare it to prpt_proj and prpt_code sheet PS1. If they both match then...

1. Let's get this one clear: you mentioned four columns but are saying that "if both match". Which two should match? Do you want prep_proj to match prpt_proj AND prep_pay match prpt_code?
2. Does it have to be INDEX/MATCH and VLOOKUP? Can it be Power Query? This seems like a reccuring task, PQ would be great for it.

 

[jpd126]

1. Let's get this one clear: you mentioned four columns but are saying that "if both match". Which two should match? Do you want prep_proj to match prpt_proj AND prep_pay match prpt_code? RESPONSE: That is correct. I need these two fields (or more) to MATCH and if they do display prpt_long in the "PR_PAY_DESCRIPTION" column.

2. Does it have to be INDEX/MATCH and VLOOKUP? Can it be Power Query? This seems like a reccuring task, PQ would be great for it. RESPONSE: I was hoping to try lookup to start, but if Power Query is a viable alternative I am willing to try. I don't have any experience with PQ.

I appreciate any support you can provide!

 

[datatronics505]

Based on what you described as a requirement that I tested on the sample data you provided, there was no match in the sample data pool. Which doesn't mean there won't be in your complete data pool. Tested with an Excel formula, the simple IF(AND()) statement, based on what you provided as a requirement:

IF(AND([@[prep_proj]]=Table2[@[prpt_proj]], [@[prep_pay]]=Table2[@[prpt_code]]), Table2[@[prpt_long]], "No Match")

there were no matches. Same with Power Query.
Try to revise your requirements- are you sure that was what was required of you? Or maybe reorganize your sample data. As it is, when you asked to search for matches and pull only those that satisfy BOTH requirements: prep_proj = prpt_proj AND prep_pay=prpt_code, no match was found. Again, that is with sample data you provided.

 

[jpd126]

I verified the data and its correct, but I am getting no match as well with the LIVE data. I tried xlookup instead and I got results. I am working to verify the data and that is working. I also want to research the advantages/disadvantages to using it.

=XLOOKUP(A3&G3,prpaytyp!A:A&prpaytyp!B:B,prpaytyp!E:E,0,0)

 

[datatronics505]

One way to verify that you are not getting false positives is to make a new column fo each part of the condition:

[@[prep_pay]]=Table2[@[prpt_code]

and in the new column 2:

[@[prep_proj]]=Table2[@[prpt_proj]]

and then a third new column where you get only TRUE or FALSE when you compare values from those two new columns with AND.
As far as XLOOKUP, it was actually introduced as an upgrade to the lookups via VLOOKUP INDEX MATCH combinations. No need to find column's number so the formula isn't as brittle.