How to modify Excel formula?

[sezuh]

Hi,
i have list in column"E" and "G" and excelformula to get result in column"F" ,what change do i have to make to the formula if i have lists in column"E","G","H" or more, and get the same result?
thanks in advance for any help or suggestions.
here an example;
Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 292px"><COL style="WIDTH: 534px"><COL style="WIDTH: 582px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>List1</TD><TD> </TD><TD>List2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,11 12 13 14 15 </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial">16 17 18 19 20 ,21 22 23 24 25 ,26 27 28 29 30 ,31 32 33 34 35 ,36 37 38 39 40 ,41 42 43 44 45 </TD><TD>15 16 24 34 36 ,26 27 28 29 30 ,41 42 43 44 45 ,21 22 23 24 25 ,31 32 33 34 35 ,41 42 43 44 45 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,11 17 25 35 37 </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial"> </TD><TD>15 17 22 26 45 ,31 32 33 34 35 ,36 37 38 39 40 ,21 22 23 24 25 ,36 37 38 39 40 ,41 42 43 44 45 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,11 18 23 27 41 </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial"> </TD><TD>15 30 32 37 41 ,16 17 18 19 20 ,21 22 23 24 25 ,26 27 28 29 30 ,31 32 33 34 35 ,36 37 38 39 40 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,11 26 33 38 42 </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial"> </TD><TD>16 17 18 19 20 ,21 22 23 24 25 ,26 27 28 29 30 ,31 32 33 34 35 ,36 37 38 39 40 ,41 42 43 44 45 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,12 18 21 31 38 </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial"> </TD><TD>16 17 18 19 20 ,21 22 23 24 25 ,31 32 33 34 35 ,26 27 28 29 30 ,31 32 33 34 35 ,41 42 43 44 45 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,12 19 24 28 42 </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Arial"> </TD><TD>16 17 18 19 20 ,21 22 23 24 25 ,36 37 38 39 40 ,26 27 28 29 30 ,36 37 38 39 40 ,41 42 43 44 45 </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F2</TD><TD>{=LOOKUP("a",IF({1,0},"",INDEX($G$2:$G$7,MATCH(TRUE,MMULT(--ISNUMBER(FIND(" "&RIGHT("0"&{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45},2)&" ",SUBSTITUTE(" "&E2&" "&$G$2:$G$7&" ",","," "))),{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})=45,0))))}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>list1</TD><TD>Result</TD><TD>List2</TD><TD>List3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,11 12 13 14 15 </TD><TD>16 17 18 19 20 ,21 22 23 24 25 ,26 27 28 29 30 ,31 32 33 34 35 ,36 37 38 39 40 ,41 42 43 44 45 </TD><TD>15 16 24 34 36 ,26 27 28 29 30 ,41 42 43 44 45 </TD><TD>21 22 23 24 25 ,31 32 33 34 35 ,41 42 43 44 45 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,11 17 25 35 37 </TD><TD> </TD><TD>15 17 22 26 45 ,31 32 33 34 35 ,36 37 38 39 40 </TD><TD>21 22 23 24 25 ,36 37 38 39 40 ,41 42 43 44 45 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,11 18 23 27 41 </TD><TD> </TD><TD>15 30 32 37 41 ,16 17 18 19 20 ,21 22 23 24 25 </TD><TD>26 27 28 29 30 ,31 32 33 34 35 ,36 37 38 39 40 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,11 26 33 38 42 </TD><TD> </TD><TD>16 17 18 19 20 ,21 22 23 24 25 ,26 27 28 29 30 </TD><TD>26 27 28 29 30 ,31 32 33 34 35 ,41 42 43 44 45 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,12 18 21 31 38 </TD><TD> </TD><TD>16 17 18 19 20 ,21 22 23 24 25 ,31 32 33 34 35 </TD><TD>26 27 28 29 30 ,36 37 38 39 40 ,41 42 43 44 45 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>01 02 03 04 05 , 06 07 08 09 10 ,12 19 24 28 42 </TD><TD> </TD><TD>16 17 18 19 20 ,21 22 23 24 25 ,36 37 38 39 40 </TD><TD>31 32 33 34 35 ,36 37 38 39 40 ,41 42 43 44 45 </TD></TR></TBODY></TABLE>
Kind regards
Sezuh

 

[sezuh]

hi,
Any suggestion please?
Thanks

 

[barry houdini]

It's not easy if you want the lookup range to be multiple columns....

One way might be to evaluate each column separately, e.g. for G2:G7 and H2:H7 try

=LOOKUP("a",CHOOSE({1,2,3},"",INDEX($H$2:$H$7,MATCH(TRUE,MMULT(--ISNUMBER(FIND(" "&RIGHT("0"&TRANSPOSE(ROW(INDIRECT("1:45"))),2)&" ",SUBSTITUTE(" "&E2&" "&$H$2:$H$7&" ",","," "))),ROW(INDIRECT("1:45"))^0)=45,0)),INDEX($G$2:$G$7,MATCH(TRUE,MMULT(--ISNUMBER(FIND(" "&RIGHT("0"&TRANSPOSE(ROW(INDIRECT("1:45"))),2)&" ",SUBSTITUTE(" "&E2&" "&$G$2:$G$7&" ",","," "))),ROW(INDIRECT("1:45"))^0)=45,0))))

If you want to add another column then repeat the red part for the relevant range....and increase {1,2,3} to {1,2,3,4}......

 

[sezuh]

Hi Barry,
thanks very much for your formula, i tried it on same list on post #1
but some how did not work ,even though there were no error,result was just blank any idea why?
I was 100% sure it would work because its your formula,whenever i see your post i try to read it ,because sometime you come up with amazing formula like magic......there are another 3 or 4 people i always look with anticipation for their post in this forum.
Thanks again
Sezuh

 

[barry houdini]

Hello Sezuh,

Do you mean the list at the bottom of post 1? Perhaps there's something missing but looking at that I don't see any entries in G2:H7 that match, don't you need E2 plus another cell to cover all numbers from 1 to 45? As E2 has 1 to 15 you need another cell with 16 to 45.......or have I missed something?

 

[sezuh]

Yes Barry
you need "E2"plus another 2 cells from "G2:H7" to cover all numbers from 1 to 45 ,and G7 +H7 would complete E2.
Thanks

 

[sezuh]

Hi,
The formula work for the the list at the top of post #1,but does not work for the list at the bottom of post #1 and unfortunately that what i want,
to complete "E2"i need one cell from "G" and one cell from "H" to cover all numbers from 1 to 45.Is that possible,if its how?
thanks for your help ,and sorry if i'm wasting your valuable time,much obliged.
Sezuh

 

[sezuh]

hi,
is it possible to change formula so it can work for the list at the bottom of post #1?
Any suggestion would be much appreciated.

 

[careboy]

It is good,thanks guys.

 

[sezuh]

Thanks for removing "Dvd sales" part in this tread much appreciated.
So any help on the subject of furmula?