ARE CASE STATEMENTS POSSIBLE TO POPULATE A CELL IN EXCEL??? 1

[TOTCOM11]

I have 3 items in a drop down in cell F9, BONE, TRIM, AND TIME. I am trying to get cell J22 to populate based on a lookup (which I currently have working) on different columns depending on what is in cell F9. I know how to do this using an IF Statement, but it can get very confusing to code all of that. Is there any function in Excel that allows you to write a case statement like you would in VBA in Access?

For instance, if F9="BONE", I want J22 to look up in column 3 (or C). If F9="TRIM", I want J22 to look up in column 4 (D). You get the idea. Is there any simple way of doing this, or do I have to have a run-on IF STATEMENT?

 

[KenWright]

Don't know if you are using VLOOKUP or just LOOKUP, or what data is being looked up, but this may give you an idea:-

Assuming you were looking up a value from A1 and were looking it up in Column B and wanted a corresponding value from either column C/D/E depending on what was in F9, then you can use the MATCH function in place of the column argument in the VLOOKUP function. You need to add 1 to the result of the MATCh statement though as it will give you either 1,2,3 and you want 2,3,4 (or just add a bogus value as the first value in the array).

=VLOOKUP(A1,$B$1:$E$19,MATCH(F9,{"BONE","TRIM","TIME"},0)+1,0)

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[TOTCOM11]

I forgot to mention two things. Yes, I am using VLOOKUP. Unfortunately I am trying to look something up that is on a separate worksheet in the same workbook.

Here is the formula that I use to "manually" bring up (without the dropdown box) the values I want for BONE.

=IF(VLOOKUP(AD8,'Product List'!1:65536,3)="","",VLOOKUP(AD8,'Product List'!1:65536,3))

I tried messing around with the MATCH function, but I couldn't get it to work the way I wanted it to. Any suggestions?

Chris

 

[TOTCOM11]

BTW.....AD8 is the cell I am using to find the particular record I'm looking for. I figured you could figure that out on your own, but just in case...

 

[TOTCOM11]

OK! I got it to work for BONE and TRIM. What I ALSO forgot to mention (sorry) is that for the occurances that F9 = "TIME", I want Excel put place the value from F18 into J22 (on the same sheet as the F9 lookup cell). How can I do this?

 

[SkipVought]

Here's how that code might look in VB...

'IF(VLOOKUP(AD8,'Product List'!1:65536,3)="","",VLOOKUP(AD8,'Product List'!1:65536,3))
    Dim n
    
    n = Application.VLookup([AD8], Sheets("Product List").Cells, 3)
    
    If Not IsError(n) Then
        Cells(22, "J").Value = n
    End If

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[TOTCOM11]

I've pretty much gotten it to work now. There are some spots in the lookup table that are blank. When the lookup on the main page comes across a blank, I want it to display nothing (AKA ""). Right now it is displaying a ZERO (0) and I don't want it to display anything. How can I get this to happen?

Here is the code I currently have in case you are interested:

=IF(F9="TIME",F18,VLOOKUP(AD8,'Product List'!1:65536,MATCH(F9,{"BONE","TRIM"},0)+2,))

 

[xlbo]

use conditional formatting to hide the zeroes

Format>Conditional formatting

Cell Value = 0
set font to white

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question