Combining VLOOKUPs into IF statement?

[kolfinna]

Hi, all.

I have a spreadsheet with a single range "ALL_DSGN" that I need to produce multiple results based on varying criteria.

My current formula is this, but it only captures half of what I need:
=IF($G12="","",IF(VLOOKUP($B12,ALL_DSGN,4,0)=4,"Project",""))

This tests that if cell G12 is empty, don't continue the formula. Else, lookup the ticket number in B12 within column 4 of the named range, and if that result equals "4," output "Project" in that original cell ($A12).

I need to have the formula also lookup in the same range and column that if the result =2, then output "Reclaim."
=IF($G12="","",IF(VLOOKUP($B12,ALL_DSGN,4,0)=2,"Reclaim",""))

I have no idea how to do this effectively. May I please get some help merging these formulae?

Thanks!

[pjwhitfield]

Please Login or Register  to view this content.

if it hits an error its more than likely theres a bracket missing/in wrong place as Im not near Excel to test.

[ConneXionLost]

How about:

Please Login or Register  to view this content.

Cheers,

[daffodil11]

One possible solution:
=IFERROR(IF($G12="","",CHOOSE(VLOOKUP($B12,ALL_DSGN,4,0),"","Reclaim","","Project")),"")

Edit: hilariously similar results. And I thought I was being obtuse.

[kolfinna]

You all are fantastic! Thank you so much. Can this formula expand to encompass three or more criteria?

=IF(ISERROR(SEARCH("SBP:",$A4,1)),3,1)

For example, I'm checking in cell $A4 if "SBP" is somewhere within that line. Right now, if it's SBP, it returns value "1". If not, value "3". I also need it to return 2 for Reclaim and 4 for Project.

I assume the expanded formula would be similar? I only know enough about formula writing to break things. A lot.

Thanks!

Edit: The CHOOSE formula is magical. How does it even work without specifying the =2 or =4?

[daffodil11]

+1 for breaking things

Please Login or Register  to view this content.

[kolfinna]

Okay, so jumping topics but I'm dreaming here:

Would it be possible to place a timestamp in one cell when another cell changes to a specific criteria? For example, I type "complete" in cell $G2 on one spreadsheet (that has its own named range "RAW_DATA"), and then on another sheet it says something like if(vlookup($B2,RAW_DATA,7,0),"complete",<timestamp that doesn't change the next time the spreadsheet is open?>))

I greatly appreciate any thoughts you have on this--even if I need to open up a new thread to address it. :D

Thanks!

[kolfinna]

Daffodil11,

When I type your formula as written, I get a "too many arguments" error. It then highlights "1". What do I do next? I'll keep reading up on the choose formula to get my head around the logic too...

Thanks.

[daffodil11]

Whoops. I broke something. That's what I get for freehanding. Remove the extra paren before seach on each line.

Please Login or Register  to view this content.

[kolfinna]

You are amazing, thank you again. :D

[daffodil11]

Choose selects the option you tell it to.

Parameters:
=CHOOSE(choose which output, output1,output2,output3,etc)
=CHOOSE(3,"UN","DEUX","TROIS") selects the third output "TROIS"

In my formula, if it finds SBP the IF outputs a 1, outputs a 2 for Reclaim, and a 3 for Project. If it doesn't find any, it outputs a 4.

It then chooses a the Nth output of "SBP","RECLAIM","PROJECT","". The default output of 4 for no match selects the empty set.

[kolfinna]

One more question on the choose function: can I add an "AND" qualifier? For example, I have the following:

Please Login or Register  to view this content.

For what it's worth, the "lolwut?" piece has never worked. :P

I need to add a piece for ABC005, which has to match both PEAR and RECLAIM in $A2. This formula is one I got help with a long time ago at its initial creation, but it's been a couple years...

[daffodil11]

It would probably be simpler in VB code.

My empty set only worked because we were working with direct IF outputs. In your example, I'd run with:

=IFERROR(CHOOSE(MAX(COUNTIF(A2,{"*BALL*","*CATS*","*ZOO*","*PEAR*"})*{1,2,3,4}),"ABC001","ABC002","ABC003","ABC004"),"lolwut?")

[kolfinna]

What if I did something like this?

=IFERROR(CHOOSE(MAX(COUNTIF(A2,{"*BALL*","*CATS*","*ZOO*","*PEAR*","*PEAR*RECLAIM*"})*{1,2,3,4,5}),"ABC001","ABC002","ABC003","ABC004","ABC005"),"lolwut?")

Am I on the right track? Or would an ampersand be needed to join those two criteria together?
Pear = ABC004
"PEAR"&"RECLAIM"=ABC005

Or something?

[daffodil11]

Uhhnnn. I just had to think a little outside the box. Sometimes a little outside the brain.

=CHOOSE(MAX(COUNTIF(A2,{"*BALL*","*CATS*","*ZOO*","PEAR","*PEAR*RECLAIM*"})*{1,2,3,4,5}),"ABC001","ABC002","ABC003","ABC004","lolwut?")

Edit: You were totally on the right track.

[kolfinna]

Woohoo! :D

It's wonderful to finally automate crap I've been doing manually forever. *lol*

In theory, I can build another type of choose statement that matches on OR criteria... such as:

OR(SEARCH($A2,"Paris","Berlin")"EMEA")
but then do that for AMER and APAC as well, with AMER being the "leftover" pile.

So: the presence in $A2 of Berlin or Paris sums up into "EMEA" region in cell $I2, while Tokyo, Hong Kong, Shanghai, or Sydney sum up into "APAC" in the same $I2. All other locations e.g., Boston, St. Louis, etc, just return "AMER."

Does that make sense? I would assume I need a choose statement, with OR criteria? Or nested stuff?

Thanks!

[kolfinna]

Maybe something like this?

Please Login or Register  to view this content.

EDIT: That only sort of worked. The below is also broken...

Please Login or Register  to view this content.

[daffodil11]

As far as your little timestamp is concerned, that's doable as well. It's done with something called Iterative Calculation, or Circular References.

Normally, people try to avoid them, but when used intentionally they can be pretty useful as well. I can't research it presently, but perhaps open up another thread about it.

[kolfinna]

Got the Iterative Calculation! :D Thanks.

[daffodil11]

I need to run because I've been in the office eleven hours, but I'd recommend putting together a sample and attaching it here so we can compare from the same point of view.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

[kolfinna]

Here is the desensitized data, I'm focusing only on the yellow column.

Thanks so much!

[daffodil11]

F2:
=IFERROR(LOOKUP(2,1/(ISNUMBER(SEARCH($H$12:$H$17,A2))),$I$12:$I$17),"AMER")

And copy down.

If you don't wish to store a table of data, you could also use:
=IFERROR(LOOKUP(2,1/(ISNUMBER(SEARCH({"Tokyo";"Sydney";"Hong Kong";"Shanghai";"Paris";"Berlin"},A2))),{"APAC";"APAC";"APAC";"APAC";"EMEA";"EMEA"}),"AMER")

[kolfinna]

Hi!

So, I'm looking at the formula--are you proposing that Columns H and I house the table? What if I use a named range (Table_Location) on the SLA tab?


Also, I thought I knew what I needed to do to fix the dates on the target finish, but apparently my solution (copying over the VBA module) didn't fix it entirely. Grumble.

Thanks!

[daffodil11]

I recommend reposting in the VBA Forums. The Custom Functions are a little beyond. I'm not too shabby at a number of procedures, but UDF's and I do not get along.

[kolfinna]

Thanks so much again for your help!

~K