Function

  • Thread starter Thread starter rajpoot_jee
  • Start date Start date
R

rajpoot_jee

Hi,

A B C D
1 150 300 450 600
2 2 20 50 70 80
3 4 40 60 90 100
4 6 60 80 100 120
5 8 80 110 130 150


How I can assign a function to get the value of 2 - 150 in Cell E2 IF I type
2 in cell F2 & 150 in cell F3
 
Hi,
Not clear what you want to achieve, if you type 2 in cell F2 you want to get
150 in cell E2 and F3 ?? if yes in cell E2 and F3 enter the formula

=if(F2=2,150,"")

I assume that if F2 is not 2 you want the cell to stay blank
 
Your example data is not clear to me... I assume the A,B,C,D is the column
headers and the 1,2,3,4,5 is the row headers, but what is the free floating
2,4,6,8 supposed to be? If it is supposed to be in Column A, then that means
your data values stretch to Column E, not Column D as shown. The problem is,
your question says you are putting one of the lookup values in Column E
which would then overwrite a table value. So, for the formula below, I am
assuming the 150,300,450,600 lookup values are in B1:E1 and the 2,4,6,8
lookup values are in A2:A5 (this corresponds to what you posted) and that
the column lookup value is in F2 and the row lookup value is in G2. With
these assumptions, here is a formula to do what (I think) you asked...

=INDEX(A1:E5,MATCH(F2,A1:A5),MATCH(G2,A1:E1))
 
Back
Top