Insert variable into formula

[Brutalic]

Hello everyone, I have a question regarding variables in formulas, I'll just give you the data for my exact case.
So I have a drop down list on the first page in the D column, which gets its data from a different worksheet (in my case named TIV). In the TIV worksheet there are also prices listed for each element that is in the drop down list. What I want to do is to get the prices to change accordingly, when I select random elements from the drop down list.

I tried to enter them manually;

=IF(ISNUMBER(FIND(TIV!B12;D34));TIV!G12; ... and so on to IF(ISNUMBER(FIND(TIV!B86;D34));TIV!G86;

If i enter them manually the huge formula actually works, but it is only up to 64 elements, and here i have 72!
Is there any way to tell Excel to connect B12-G12 up to B86-G86? Like making a variable X (12-86) and writing BX GX or something, I'm not sure.

Any ideas?
Thanks, Luka.

 

[patel45]

use macro

 

[Brutalic]

use macro

Thanks for the answer, but a little more info would be greatly appreciated

 

[pjmorris]

Hi Brutalic,

Assuming that the cell with the dropdown (I assume you're using validation to create the dropdown) is cell B2, then in cell C2 use =IFERROR(VLOOKUP(B2,Sheet2!B2:C6,2,FALSE),"") which uses the vlookup function to return the value, the iserror function merely ensures that if there is no information in B2 that the cell then shows blank rather than #NA. Usefully the validation will be based on the list in cells B2:B6 on sheet2 with the prices in C2:C6.

Hope this helps.

 

[patel45]

Thanks for the answer, but a little more info would be greatly appreciated

the same for me

 

[Brutalic]

Hi Brutalic,

Assuming that the cell with the dropdown (I assume you're using validation to create the dropdown) is cell B2, then in cell C2 use =IFERROR(VLOOKUP(B2,Sheet2!B2:C6,2,FALSE),"") which uses the vlookup function to return the value, the iserror function merely ensures that if there is no information in B2 that the cell then shows blank rather than #NA. Usefully the validation will be based on the list in cells B2:B6 on sheet2 with the prices in C2:C6.

Hope this helps.

Thanks, I will try this when I get into work on Thursday. I'll leave some feedback whether or not it worked, or maybe some additional questions . Thanks again.

 

[Brutalic]

Hi Brutalic,

Assuming that the cell with the dropdown (I assume you're using validation to create the dropdown) is cell B2, then in cell C2 use =IFERROR(VLOOKUP(B2,Sheet2!B2:C6,2,FALSE),"") which uses the vlookup function to return the value, the iserror function merely ensures that if there is no information in B2 that the cell then shows blank rather than #NA. Usefully the validation will be based on the list in cells B2:B6 on sheet2 with the prices in C2:C6.

Hope this helps.

It worked, I had to customize the formula a little bit, but you pointed me in the right direction, thanks a lot!