vlookup + criteria

[jw01]

Hi guys

I am having some issues trying to embed two formulas together or not sure where to begin.

I have a list of account numbers that the dollar amount they have sold. That portion is pretty simple, however, one account number is listed multiple times under different headings by "dollar type" eg. coop or VB or new launch.

Now, I am only interested in getting the dollar amount that is listed as coop, as an example.

I tried this formula that I saw online but not sure how to make it work with my formula

=IF(ISERR(SEARCH(W$9,'RB1'!J6)),"",VLOOKUP($D10,'RB1'!$E$6:$CR$1719,58))

RB1 = sheet that contains "dollar type" and "sold amount".
W9 = header that shows "coop"


Any idea on how I can achieve this? thanks so much!

[newdoverman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[jw01]

Hey guys

Pls see the attached sheet.

I have highlighted in "green" the column from the RB1 sheet that I need the dollar amounts from.

FYI - from the RB1 sheet

column E = office #
column J = dollar type
column BJ = dollar amount

Thank you in advance.

[newdoverman]

I'm taking a guess here that you want to match the head-office number and the PLT COOP or PLT VB with the value in RBI column BJ

On the RBI worksheet I entered this formula in column A to determine if PLT COOP or PLT VB are in RBI COLUMN J

Please Login or Register  to view this content.

On OVERVIEW W10 enter this formula and fill down

Please Login or Register  to view this content.

On OVERVIEW X10 enter this formula and fill down

Please Login or Register  to view this content.

BOTH FORMULAE are array formulae.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[jw01]

Hi there

thanks so much, that works awesome.

One final refinement and I am not sure how to add it. Rather, I tried but it was is not working.

There is another iteration of the way "PLT Coop" is written "2015 CO-OP PLT" and "2015 PLT CO OP ADVERT" -> is there a way to add in "Co-op" as well?

I tried to modify the formula

=IFERROR(IF(D6="","",IFERROR(IF(AND(SEARCH("*PLT*",J6),SEARCH("*COOP*",J6),SEARCH("*CO-OP*",J6))=TRUE,1,""),IF(AND(SEARCH("*PLT*",J6),SEARCH("*VB*",J6))=TRUE,2,""))),"")

^ see the part in bold, however it removes the 1 from lines where it shows "Coop"?

Any idea on how to include that?

Thanks a million!

[bebo021999]

Try in W10:

Please Login or Register  to view this content.

X10:

Please Login or Register  to view this content.

Both are confirmed by Ctrl-shift-enter.

[jw01]

Try in W10:

Please Login or Register  to view this content.

X10:

Please Login or Register  to view this content.

Both are confirmed by Ctrl-shift-enter.

Hey there

Thanks so much for your input.

The formula in W10 is not working properly. For example, for the first headoffice 7430624 -> if you manually filter column E in the RB1 sheet, you will see rows 1632+1634+1636+1638 state "2015 PLT COOP ADVERT", and in column BJ, it has value of 44K

The formula above is showing 0 when it should read 44K.

I did press cntrl+shift+enter

Any idea on whats not working? thank you

[jw01]

Guys

sorry, but one main thing i forgot to mention....the formula needs to "SUM" the values in the respective column eg. column BJ from RB1

The formulas listed above take the first value that shows up.

Any way to embed SUM into the formula?

thank you in advance.

[bebo021999]

Try again:

W10:

Please Login or Register  to view this content.

X10:

Please Login or Register  to view this content.

Opp, paid no attention to new request in #8. Will be back later.

[bebo021999]

Things should be easier with sum:

W10:

Please Login or Register  to view this content.

X10:

Please Login or Register  to view this content.

Non array formula, enter only.

[newdoverman]

If you want the formula that I gave you to work change COOP to CO*OP in the formula on the RBI worksheet column A. This will return both COOP and CO-OP.

[bebo021999]

... change COOP to CO*OP in the formula ...

You mean "CO?OP" ?
"CO*OP" including COOOOOP, i think.