I have a google sheets cell with 5 different texts (from a dropdown) - how can I make another cell r-CodePudding

The 5 values in J4 are Studio, 1, 2, 3, 4. Mortgage! is another sheet where I want to take B2*(52/12) if studio, B3*(52/12) if 1 and so on

Here is what I have been working with. I think I am close I just can't get it together to work

If  
$J$4='Studio',(Mortgage!B2*(52/12) 
$J$4='1',(Mortgage!B3*(52/12) 
$J$4='2',(Mortgage!B3*(52/12) 
$J$4='3',(Mortgage!B4*(52/12)
$J$4='4',(Mortgage!B5*(52/12)

CodePudding user response：

Your logic is acceptable. You need to use the correct syntax.

=IF(  
$J$4='Studio',Mortgage!B2*(52/12), 
IF($J$4='1',Mortgage!B3*(52/12), 
IF($J$4='2',Mortgage!B3*(52/12), 
IF($J$4='3',Mortgage!B4*(52/12),
$J$4='4',Mortgage!B5*(52/12)
))))

By the way, it is customary to post a link to a public Google Sheet showing sample data when asking questions about Google Sheets.

CodePudding user response：

Firstly, I think you have an errory in your sample code as you say I want to take B2*(52/12) if studio, B3*(52/12) if 1 and so on, but your sample is doing (Mortgage!B3*(52/12) for both $J$4='1' and $J$4='2'.

Therefor, I will assumne you are instead expecting...

if
$J$4='Studio',(Mortgage!B2*(52/12) 
$J$4='1',(Mortgage!B3*(52/12) 
$J$4='2',(Mortgage!B4*(52/12) 
$J$4='3',(Mortgage!B5*(52/12)
$J$4='4',(Mortgage!B6*(52/12)

As others suggested, you can achive this by applying a nested IF() function with correct syntax,

but other than standard IF() funciont, you can use IFS() function for these kinds of cases:

=IFS(  
$J$4='Studio',Mortgage!B2*(52/12),
$J$4='1',Mortgage!B3*(52/12), 
$J$4='2',Mortgage!B4*(52/12), 
$J$4='3',Mortgage!B5*(52/12),
$J$4='4',Mortgage!B6*(52/12)
)

There is also a SWITCH() function in google sheet that do similar things:

=SWITCH($J$4,
'Studio',Mortgage!B2*(52/12),
'1',Mortgage!B3*(52/12),
'2',Mortgage!B4*(52/12),
'3',Mortgage!B5*(52/12),
'4',Mortgage!B6*(52/12)
)

You may even create your own array to lookup for result:

=LAMBDA(DATA,
 VLOOKUP($J$4,DATA,2,FALSE)
)({
  'Studio',Mortgag!B2*(52/12);
  '1',Mortgage!B3*(52/12);
  '2',Mortgage!B4*(52/12);
  '3',Mortgage!B5*(52/12);
  '4',Mortgage!B6*(52/12)
 })

where the array part:

{
  'Studio',Mortgag!B2*(52/12);
  '1',Mortgage!B3*(52/12);
  '2',Mortgage!B4*(52/12);
  '3',Mortgage!B5*(52/12);
  '4',Mortgage!B6*(52/12)
 }

can be shortened into something like this:

ArrayFormula({{'Studio';'1';'2';'3';'4'},Mortgage!B2:6*(52/12)})

ArrayFormula({{'Studio';TO_TEXT(SEQUENCE(4))},Mortgage!B2:6*(52/12)})

Combine this with the VLOOKUP(), you get this:

=ArrayFormula(VLOOKUP($J$4,{{'Studio';TO_TEXT(SEQUENCE(4))},Mortgage!B2:6*(52/12)},2,FALSE))

XLOOKUP() function also works in a similar way as VLOOKUP():

=ArrayFormula(XLOOKUP($J$4,{'Studio';TO_TEXT(SEQUENCE(4))},Mortgage!B2:6*(52/12)))