excel lookup and then return a formula

**raider573** · 02-25-2017, 10:45 AM

I am not sure this can be accomplished in excel but I thought I would throw it out there. I have a table with filing information for various states and need a formula that will look to the table in another worksheet within the workbook called "Filing Matrix" for a particular state and then based on certain criteria within a cell return a formula for that state. Below is an excerpt from the table.

Filing Matrix worksheet

A B C
state Aggregate Due Diligence

AZ <$50 >=$50
CA <$25 >=$50
IL <=$5 >$10
MI <$25 >=$25
NY <$20 >=$20
TX <$25 >$25

I have been using a formula for all the lookups in the table and it has worked well but I can't figure out how to perform the look up for the above example and then return a formula.

Worksheet 1

A B C D
State Amt Aggregate Due Diligence

AZ $20
AZ $65
CA $60
CA $15
IL $03
IL $15
MI $35
MI $12
NY $10
NY $25
TX $18
TX $30

Required formula for column "C" above in worksheet 1: I need a formula that will look to the state code in column "A" of worksheet 1 and then match the code to the state code in the table "Filing Matrix" worksheet, read across to the column "B" for the criteria (e.g. AZ - <$50) and then compare the amount in the worksheet 1 at column "B" Amt. and then if the Amt in column B is below the criteria (e.g.,for AZ <$50) then return the amount in Column "B" (e.g., AZ $20) worksheet 1. If the amount in column "B" exceeds the criteria (e.g. <$50) then return nothing as in the case of AZ second line $65, which is greater than the $50 requirement.

The required formula for column "D" above in worksheet 2: I need a formula that will follow the same logic as the previous formula but instead of looking to column "B" in the Filing Matrix, will look to column "C" (e.g., AZ >=$50) and return a value of "YES" where the amount in column "B" meets the particular state requirement for due diligence and where it doesn't, simply leave the cell blank. Please note, that I have a formula for the column "D" but I have to sort the worksheet by state code then enter the formula manually and copy down to the next state code, where the criteria may change. I am trying to avoid the manual process through a lookup formula or any other applicable logic. Thanks

**etaf** · 02-25-2017, 11:12 AM

that should be straight forward - using an IF with the lookup
and just put the value in the lookup column B - not < or > just the value
the formula will do the rest

=if(vlookup(a2,'Filing Matrix'!$A$1:$B$100, 2, false) < = b2, vlookup(a2,'Filing Matrix'!$A$1:$B$100, 2, false), 0)

on c
=if(b2 <> 0, "yes", "")
as if it does not meet it will be 0 from the look up in B2

otherwise
Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**raider573** · 02-26-2017, 09:04 AM

Thanks for assistance, I have provided you the attached workbook so that you see the desired results highlighted in yellow versus the results achieved using the formula

**JeteMc** · 02-26-2017, 10:45 AM

This proposed solution uses several helper columns, which may be hidden for aesthetic purposes.
On the filing matrix sheet columns N:O are taking the dollar sign out of what is in column L. The formula for column N is:

Formula:

Please Login or Register  to view this content.

The formula for column O is:

Formula:

Please Login or Register  to view this content.

On the detail sheet the formula in column F uses the following formula to make an 'evaluation' text string:

Formula:

Please Login or Register  to view this content.

Using the Define Name on the Formula tab the formula =EVALUATE(DETAIL!$F3) is applied to the name Result. Column G is then populated using the formula:

Formula:

Please Login or Register  to view this content.

Column H is populated using the formula:

Formula:

Please Login or Register  to view this content.

Notice that CT and ID don't work properly due to the 'No' input as the aggregate for those states. Note file extension changed to .xlsm to accommodate Defined Name formula.
Let us know if you have any questions.