IF or IFS Functions

[Galtin]

Hi,

New here.

This may be a very simple question:

1. Worksheet 2:
   1. I have a list of 30 apartment complexes, cells A1:A30.
   2. Cells B1:B30, there is a corresponding dollar amount for each apartment complex.
2. Worksheet 1:
   1. In cell A1, I have a dropdown list with the names of the apartment complexes.
   2. In cell B1, I would like to be able to state the following:
   3. Whichever apartment complex is selected in A1 then the corresponding dollar amount for that apartment complex is placed in cell B1.

Worksheet 1 Current Formula:
=IFS(A1=Sheet1!A1,Sheet1!B1,Sheet1!A2,Sheet1!B2...)

This isn't working for me?

 

[Galtin]

I forgot to clarify:
Worksheet 2 is called Master.
Worksheet 1 is called Allocations.

The formula is for worksheet Allocations is:
=IFS(A1=Master!A1,Master!B1,Master!A2,Master!B2...)

 

[Marcelo Branco]

Try VLOOKUP

=VLOOKUP(A1,Master!A1:B30,2,0)

M.

 

[Galtin]

Marcelo,

Thank you so very much - I believe that did it. Please help me understand what the 2 is for?

You have no idea how relieved I am

 

[Joe4]

Please help me understand what the 2 is for?

It says to return the second column from you lookup range. Since your lookup range is A1:B30, it is telling it to return the column B value.

For more details on how VLOOKUP works, see: https://www.techonthenet.com/excel/formulas/vlookup.php

 

[Galtin]

Thank you, Joe!

 

[Galtin]

Hello, I have an additional question to go along with my previous request:

Using the same scenario above but want to add additional info from two additional columns, can I:

=VLOOKUP(A1,Master!A1:B30,2,0) and VLOOPUP(A1,Master!C1:D30,?,0)) - is this correct?

Lastly, is it possible to VLOOKUP data when the columns are not side by side - in other words, VLOOKUP cells A1:A30 that contains the names of the properties and cells F1:F30 that contains the dollar amounts?

Thank you,

 

[Joe4]

Your lookup range can span many columns. The only requirement is that the value you are "matching on" (or "looking up"), must be the left-most column in your lookup range.

So, if you wanted to lookup the value in column A, and return the value in column F, you would just use:

[COLOR=#333333]=VLOOKUP(A1,Master!A1:F30,6,0)[/COLOR]

If you wanted to return the value from column D in another column in the same row, you would just use another VLOOKUP, i.e.

[COLOR=#333333]=VLOOKUP(A1,Master!A1:F30,4,0)[/COLOR]

The third argument (the column you are returning) doesn't necessarily need to exactly match the number of rows in your LOOKUP range. It just cannot be more than that.
So, in the last example, A-F totals 6 columns, and we are returning the value from the 4th column (D). That is fine.

We could NOT do something like:

[COLOR=#333333]=VLOOKUP(A1,Master!A1:F30,8,0)[/COLOR]

as our lookup range only has 6 columns, so we cannot return the 8th column from the range (since there is no 8th column).

One other thing that often makes things easier is to used named ranges in the lookup range. The nice thing about that is the range won't shift as you copy the formula to other cells.
So, if we named the range Master!A1:F30 something like "MyRange", we could just write:

[COLOR=#333333]=VLOOKUP(A1,MyRange,6,0)[/COLOR]

See here for more details: https://www.airweb.org/eAIR/techtips/Pages/VLOOKUP-COLUMN-Function-and-Named-Ranges.aspx

Hope that clarifies things for you.

 

[Galtin]

Oh my gosh, it makes sense - I will give it a try and let you know - thank you again!

 

[Joe4]

You are welcome.