Excel Formula too long!

[Excelnoob1998]

Hi Guys!,

Can i get some help from you all and know some excel shortcut for my formula? This following is my formula: so basically what i want is that if the value of cell L2 is =s to any cell of column D (eg, L2 = D15) then it would be equals to the row of D but column of H Thank you all very much!!!!

"=IF(L2=$D$2,$H$2,IF(L2=$D$3,$H$3,IF(L2=$D$4,$H$4,IF(L2=$D$5,$H$5,IF(L2=$D$6,$H$6,IF(L2=$D$7,$H$7,IF(L2=$D$8,$H$8,IF(L2=$D$9,$H$9,IF(L2=$D$10,$H$10,IF(L2=$D$11,$H$11,IF(L2=$D$12,$H$12,IF(L2=$D$13,$H$13,IF(L2=$D$14,$H$14,IF(L2=$D$15,$H$15,IF(L2=$D$16,$H$16,IF(L2=$D$16,$H$16,IF(L2=$D$17,$H$17,IF(L2=$D$18,$H$18,IF(L2=$D$19,$H$19,IF(L2=$D$20,$H$20,IF(L2=$D$21,$H$21,IF(L2=$D$22,$H$22,IF(L2=$D$23,$H$23,IF(L2=$D$24,$H$24,IF(L2=$D$25,$H$25,IF(L2=$D$26,$H$26,IF(L2=$D$27,$H$27,IF(L2=$D$28,$H$28,IF(L2=$D$29,$H$29,IF(L2=$D$30,$H$30,IF(L2=$D$31,$H$31,IF(L2=$D$32,$H$32,IF(L2=$D$33,$H$33,IF(L2=$D$34,$H$34,IF(L2=$D$35,$H$35,IF(L2=$D$36,$H$36,IF(L2=$D$37,$H$37,IF(L2=$D$38,$H$38,IF(L2=$D$39,$H$39,IF(L2=$D$40,$H$40,IF(L2=$D$40,$H$40,IF(L2=$D$41,$H$41,IF(L2=$D$42,$H$42,IF(L2=$D$43,$H$43,IF(L2=$D$44,$H$44,IF(L2=$D$45,$H$45,IF(L2=$D$46,$H$46,IF(L2=$D$47,$H$47,IF(L2=$D$48,$H$48,IF(L2=$D$49,$H$49,IF(L2=$D$50,$H$50,IF(L2=$D$51,$H$51,IF(L2=$D$52,$H$52,IF(L2=$D$53,$H$53,IF(L2=$D$54,$H$54,IF(L2=$D$55,$H$55,IF(L2=$D$56,$H$56,IF(L2=$D$57,$H$57,IF(L2=$D$58,$H$58,IF(L2=$D$59,$H$59,IF(L2=$D$60,$H$60,IF(L2=$D$61,$H$61,IF(L2=$D$62,$H$62,IF(L2=$D$63,$H$63,IF(L2=$D$63,$H$63,IF(L2=$D$64,$H$64,IF(L2=$D$65,$H$65,IF(L2=$D$66,$H$66,IF(L2=$D$67,$H$67,IF(L2=$D$68,$H$68,IF(L2=$D$69,$H$69,IF(L2=$D$70,$H$70,IF(L2=$D$71,$H$71,IF(L2=$D$72,$H$72,IF(L2=$D$73,$H$73,IF(L2=$D$74,$H$74,IF(L2=$D$75,$H$75,IF(L2=$D$76,$H$76,IF(L2=$D$77,$H$77,IF(L2=$D$78,$H$78,IF(L2=$D$79,$H$79,IF(L2=$D$80,$H$80,IF(L2=$D$81,$H$81,IF(L2=$D$82,$H$82,IF(L2=$D$83,$H$83,IF(L2=$D$84,$H$84,IF(L2=$D$85,$H$85,IF(L2=$D$86,$H$86,IF(L2=$D$87,$H$87,IF(L2=$D$88,$H$88,IF(L2=$D$89,$H$89,IF(L2=$D$90,$H$90,IF(L2=$D$91,$H$91,FALSE"

 

[jammoca]

Is this what you want ...

=OFFSET(A1,MATCH(L2,D:D,0)-1,7)

Kind regards,

Chris

 

[Excelnoob1998]

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {font-size:12.0pt;}--></style>

item	quantity		item	Quantity
A	1		D	4
B	2		C	3
C	3		A	1
D	4		K	11
E	5		G	7
F	6		H	8
G	7
H	8
I	9
J	10
K	11

<!--StartFragment--> <colgroup><col width="87" span="5" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

I don't think that is the one i need but this is an example of what i am doing!

Thank you so much for your help and quick reply!!

 

[ukmikeb]

Hi

Try :-

=IFERROR(INDEX($H$2:$H$91,MATCH($L2,$D$2:$D$91,0)-1),FALSE)

hth

 

[Excelnoob1998]

Hello Mike!!

Thank you so much i got the formula i want after i removed the "-1" from the formula you gave me. Its great! thank you so much!

Thanks,
David

 

[Excelnoob1998]

Hello Mike!!

Thank you so much i got the formula i want after i removed the "-1" from the formula you gave me. Its great! thank you so much!

Thanks,
David

 

[jammoca]

My solution did do what you wanted ...

=OFFSET($A$1,MATCH(L2,D:D,0)-1,7)

but you've now indicated that L2 won't be the only cell you need the formula in, so the formula would be amended to the following ...

=IFERROR(OFFSET($A$1,MATCH(L2,D:D,0)-1,7),"")

Did you even try the formula ?

 

[Excelnoob1998]

hi Chris,

Sorry that formula work as well! im so sorry>.< I am a first time user for this forum. Is there any button i have to click to solve this thread?