## Wednesday, August 22, 2012

Here is what I am trying to do... I have a list of numbers (1-5) in cell A1 on my main sheet that are in the form of a Data Validation drop down menu.  That Data Validation is being generated with the data from cells A1-5 on another sheet named NUMBERS.  Also on the NUMBERS sheet are the numbers 10%-50% in cells B1-5.  I would like to create a function for cell B1 on the main sheet that tells it to display the data from cell B5 on the NUMBERS sheet, if the the number 5 is selected from the Data Validation in cell A1 on the main sheet.

So, if I select the number 3 from the Data Validation in cell A1, then B1 displays the number 30%.  2 returns 20%, 1 returns 10% and so on.

Solution:

You have to put the following formula in cell B1 (of "MAIN" sheet):
=if(A1=NUMBERS!A1,NUMBERS!B1,if(A1=NUMBERS!A2,NUMBERS!B2,if(A1=NUMBERS!A3,NUMBERS!B3,if(A1=NUMBERS!A4,NUMBERS!B4,if(A1=NUMBERS!A5,NUMBERS!B5,)))))

OR

You can also put the following formula in Cell B1 (of "MAIN" sheet) to get your result:
=FILTER(NUMBERS!B1:B5,NUMBERS!A1:A5 = A1)

Following is the screenshot of "MAIN" sheet

And following is the screenshot of "NUMBERS" sheet.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,
Kishan,

1. Kishan, this is useful to an extent. Let me complicate this further:
On the main spreadsheet I have the list of 1-10 in column A. On Numbers sheet I have 3,5,6,8,10 in column A. and a set of values in column B. I would like to copy the values from Numbers!B to the main sheet on column B based on the equivalent number on Numbers!A.

2. use vlookup, with the table being numbera!A:B and the index being main:A

3. Can someone help me find the fault in this code?