Forum Discussion
how do I do this formula in excel?
Hi Benet,
Since you don't define what shall be for number like 6.5 it looks like you consider only integers. If translate your range in form as in column B
the formula could be
=LOOKUP(F1,$B$1:$B$11,$D$1:$D$11)
and attached
That's brilliant Sergei, works perfectly. You made that seem very easy!
Many thanks, Benet
You are welcome
I have another problem!
I have two lists with four choices in each list, and I want to return an associated value from a grid of numbers when I make a choice from each list.
The lists of choices are:
- None, open, perforated, solid
- a, b, c, d
The first list basically is the headings for the columns, and the second is the headings for the rows, and the grid is populated with associated numbers.
So when I make my choices (how is this best done - with tick boxes, or maybe a drop down menu?) I would like the number in the relevant grid square to be returned to the formula cell.
None
Open
Perforated
Solid
a
300
225
150
0
b
100
75
50
0
c
50
38
25
0
d
10
8
5
0
So for example if I choose c and open, I would return a value of 38.
Any assistance on how to do the choices and the number formula would be much appreciated!
Thanks Sergei, I was trying to pick your formula apart to see how to do a simpler version too, one number relating to one drop down choice. What is the simplest way to do this please? Would I need to do a new topic for this? Please see attached. Many thanks Ben
Benet, it's always better to start new conversation for every new topic. Perhaps you are around the same project and for you all questions are the same topic.
But we here have no idea about your project and think in terms of concrete formulas and requirements for them, for us they are all separate topics.
At the same time you have more chances receive an answer or even few different answer. If the latest you may select which one is better meet your needs.
Anyway, here - yes, drop down list for Choose CSA (through data validation) and
=INDEX($D$5:$D$16,MATCH($F4,C5:C16,0))
to return the diameter. Attached.
Hello,
In your example:
=SUMPROD(($B$10:$B$13="c")*($C$9:$F$9="Open")*($C$10:$F$13))
Hello,
Read SUMPRODUCT and not SUMPROD
J-M
Hi Benet,
That is separate topic, next time please start it as new conversation.
Yes, drop down list is most suitable and the formula will be
=IFERROR(INDEX($C$10:$F$13,MATCH(J6,$B$10:$B$13,0),MATCH(I6,$C$9:$F$9,0)),"")
as for the data in attached
Another great reply, thanks Sergei.
Point regarding subject matter noted.
Cheers!
Ben
