Create formula with multiple values.

I need to create a formula that will allow me to use two sheets within the same workbook. I set up a dropdown box in my B2 cell in my first worksheet. My A4 cell is where I am trying to create my formula. I am not even sure how to say what I need. My drop down is filled with numbers from 1-211. I started my formula with =IF(B2=1,Sheet2!B3) this much I can get to work. It fills in the correct information. I do not know how to get it to continue. I need B2=2,Sheet2!B4 etc...... Can someone help me. If you need more information please let me know.

instead of using if u could use vlookup function, guess that would more serve the purpose...

can u post a sample workbook??

Okay I created a generic sample of what I am trying to do. I have my dropdowns also. On the second sheet you can see what pad number goes with which serial number or so to speak.

That worked great Ricardo. But I should have been a little clearer. The formula works up to 100 but my list doesn't go 1-211 completely. It is 1-100 then starts back up from 181-211. I have been playing with your formula to get it to work but it will only pull 1-100 and once I click on 181-211 it doesn't bring anything back.

Maybe this,

=INDEX(Serial,MATCH(B2,Pad,0))

Regards

Originally Posted by navwelch

the index function will not work since the index function picks up the data by reference to a row and a column. the above formula gives the row number to pick the data, meaning if the cell b2 in sheet 1 is 10, the function picks up data from the 10th row starting from the selection range. incidently, the range is from b2:b211. hence the 10th row from b2 is b11. hence u got the result. however when b2 is changed to 181 the 181st row from the selection range has no data!!

That's why I sugested the use of Match. Have you try the formula I provided?

=INDEX(Serial,MATCH(B2,Pad,0))

The Serial and Pad names are already created on the Christi.Scott original file.

Regards

hi sailepaty,

i fully agree with your point. i actually wanted to add only

A4: =INDEX(Sheet2!$B$2:$B$211,B2,1

as not workable, but u know in a haste to provide a correct ans(out of enthusiasm having found one!!) included yours too

infact index-match function is better than vlookup since with index-match we can get data even from the column which is to the left of the reference cell, which is not possible from vlookup.

with respect to rwgrietveld,
can you please tell the benefit of dynamic range in this problem