vlookup on 2 columns

[yaniv.dg@gmail.com]

hi trying to vlookup on 2 culomns but im sure its not the way
i need to compare 1 culomn find all the data there and with this to
compare another data in another culomn based on this 2 to bring data
from third culomn
i cannot find a solution for this

[Bob Phillips]

=INDEX(third_column,MATCH(1,(column1=value1)*(column2=value2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

although I say column1 etc., but you cannot use a whole column, only part of
it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<yaniv.dg@gmail.com> wrote in message
news:1152345833.835018.327090@m73g2000cwd.googlegroups.com...
> hi trying to vlookup on 2 culomns but im sure its not the way
> i need to compare 1 culomn find all the data there and with this to
> compare another data in another culomn based on this 2 to bring data
> from third culomn
> i cannot find a solution for this
>

[Mark Ivey]

Give this site a look over...

http://www.bygsoftware.com/Excel/VBA/vlookup2.htm

--
Mark Ivey
<yaniv.dg@gmail.com> wrote in message news:1152345833.835018.327090@m73g2000cwd.googlegroups.com...
hi trying to vlookup on 2 culomns but im sure its not the way
i need to compare 1 culomn find all the data there and with this to
compare another data in another culomn based on this 2 to bring data
from third culomn
i cannot find a solution for this

[yaniv.dg@gmail.com]

hi dave,
i think this issue is consering excel becaouse i'm searching for the
formula in excel,access is just the executer and thats it.

i need to compare between 2 culomns in order to bring the correct data
for example.
35....
36 1-5 a
36 5-7 b
36 9-18 c
36 20-25 d
37.....

so for example i want to find 36 and the number c in order to bring the
data from the third culomn
so the result would be b(36 and 6 is in the range of 5 and 7)
i can isulate the the ranges by left and right but from some reason
something is not working

index/match working perfectly on specific data but when its consering
ranges it might be diffrent

Dave Peterson wrote:
> I don't understand the question or the example and I've never used Access.
>
> Maybe someone else will jump in.
>
> yaniv.dg@gmail.com wrote:
> >
> > thank you very much for your help,it was very helpfull inforamtion that
> > i used
> >
> > now it became little bit more complex becaouse i need to find data in
> > once culomn and based on it to compare a range
> > for example:
> > 36-d a
> > 36 d-f
> > 36 k-r
> > 36 s-t
> >
> > so i need for example to find the culomn where "e" is sitting
> >
> > Dave Peterson =D7=9B=D7=AA=D7=91:
> > > =3Dindex(sheet2!a:a,match(A1,sheet2!b:b,0))
> > >
> > > Where sheet2 holds the table and A1 of the current sheet (not sheet2)=
holds the
> > > value to match (e in your example).
> > >
> > > You may want to read Debra Dalgleish's notes:
> > > http://www.contextures.com/xlFunctions02.html (for =3Dvlookup())
> > > and
> > > http://www.contextures.com/xlFunctions03.html (for =3Dindex(match()))
> > >
> > > yaniv.dg@gmail.com wrote:
> > > >
> > > > hi mark
> > > > do you this there is a way to use the current functions without vba
> > > > becaouse the execute is coming from access
> > > >
> > > > my vlookup needs to be as folow
> > > >
> > > > for example:
> > > > need to find 36 where the leter e is chosen that means:
> > > >
> > > > 36 a
> > > > 36 b
> > > > 36 c
> > > > 36 e
> > > >
> > > > so the data willcome only from the row on 36 e but the question how=
can
> > > > we monitor both of the culomns in order to find the correct row to =
use
> > > >
> > > > Mark Ivey =D7=9B=D7=AA=D7=91:
> > > > > Give this site a look over...
> > > > >
> > > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > > >
> > > > > --
> > > > > Mark Ivey
> > > > > <yaniv.dg@gmail.com> wrote in message news:1152345833.835018.32=
7090@m73g2000cwd.googlegroups.com...
> > > > > hi trying to vlookup on 2 culomns but im sure its not the way
> > > > > i need to compare 1 culomn find all the data there and with thi=
s to
> > > > > compare another data in another culomn based on this 2 to brin=
g data
> > > > > from third culomn
> > > > > i cannot find a solution for this
> > > > >
> > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00
> > > > > Content-Type: text/html; charset=3Diso-8859-1
> > > > > Content-Transfer-Encoding: quoted-printable
> > > > > X-Google-AttachSize: 1197
> > > > >
> > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > > > <HTML><HEAD>
> > > > > <META http-equiv=3DContent-Type content=3D"text/html; charset=3Di=
so-8859-1">
> > > > > <META content=3D"MSHTML 6.00.5346.5" name=3DGENERATOR>
> > > > > <STYLE></STYLE>
> > > > > </HEAD>
> > > > > <BODY bgColor=3D#ffffff>
> > > > > <DIV>Give this site a look over...</DIV>
> > > > > <DIV>&nbsp;</DIV>
> > > > > <DIV><A
> > > > > href=3D"http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http:/=
/www.bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > > <BLOCKQUOTE
> > > > > style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px;=
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > > <DIV>&lt;<A href=3D"mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.c=
om</A>&gt; wrote
> > > > > in message <A
> > > > > href=3D"news:1152345833.835018.327090@m73g2000cwd.googlegroups.=
com">news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV=
>hi
> > > > > trying to vlookup on 2 culomns but im sure its not the way<BR>i=
need to
> > > > > compare 1 culomn find all the data there and with this to<BR>co=
mpare another
> > > > > data in&nbsp; another culomn based on this 2 to bring data<BR>f=
rom third
> > > > > culomn<BR>i cannot find a solution for this<BR></BLOCKQUOTE></B=
ODY></HTML>
> > > > >
> > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00--
> > >
> > > --
> > >
> > > Dave Peterson
>=20
> --=20
>=20
> Dave Peterson

[Bob Phillips]

So you tried

=INDEX(C2:C200,MATCH(1,(H19=A2:A200)*(G19>=LEFT(B2:B200,1))*(G19<=RIGHT(B2:B
200,1)),0))

did you, and you array entered it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<yaniv.dg@gmail.com> wrote in message
news:1152512117.911223.133660@75g2000cwc.googlegroups.com...
yes i know,i tried this with ranging the culomns,it still doesnt work

Dave Peterson wrote:
> From Bob's first response:
>
> ...although I say column1 etc., but you cannot use a whole column, only
part of
> it.
>
>
>
> yaniv.dg@gmail.com wrote:
> >
> > =INDEX(C:C,MATCH(1,(H19=A:A)*(G19>=LEFT(B:B,1))*(G19<=RIGHT(B:B,1)),0))
> >
> > ok,maybe this can spread some light
> >
> > Dave Peterson ???:
> > > Post the real formula that fails for you.
> > >
> > > yaniv.dg@gmail.com wrote:
> > > >
> > > > technically it should be something like this:
> > > >
index(culomn3,match(1,(value=culomn1)*(value>left(culomn2,1))*(value<right(c
ulomn2,1)),0)
> > > > it gives #value or #n/a
> > > > (even after doinbg ctrl,shift,enter)
> > > >
> > > > but from some reason its not working
> > > >
> > > > yaniv.dg@gmail.com wrote:
> > > > > hi dave,
> > > > > i think this issue is consering excel becaouse i'm searching for
the
> > > > > formula in excel,access is just the executer and thats it.
> > > > >
> > > > > i need to compare between 2 culomns in order to bring the correct
data
> > > > > for example.
> > > > > 35....
> > > > > 36 1-5 a
> > > > > 36 5-7 b
> > > > > 36 9-18 c
> > > > > 36 20-25 d
> > > > > 37.....
> > > > >
> > > > > so for example i want to find 36 and the number c in order to
bring the
> > > > > data from the third culomn
> > > > > so the result would be b(36 and 6 is in the range of 5 and 7)
> > > > > i can isulate the the ranges by left and right but from some
reason
> > > > > something is not working
> > > > >
> > > > > index/match working perfectly on specific data but when its
consering
> > > > > ranges it might be diffrent
> > > > >
> > > > > Dave Peterson wrote:
> > > > > > I don't understand the question or the example and I've never
used Access.
> > > > > >
> > > > > > Maybe someone else will jump in.
> > > > > >
> > > > > > yaniv.dg@gmail.com wrote:
> > > > > > >
> > > > > > > thank you very much for your help,it was very helpfull
inforamtion that
> > > > > > > i used
> > > > > > >
> > > > > > > now it became little bit more complex becaouse i need to find
data in
> > > > > > > once culomn and based on it to compare a range
> > > > > > > for example:
> > > > > > > 36-d a
> > > > > > > 36 d-f
> > > > > > > 36 k-r
> > > > > > > 36 s-t
> > > > > > >
> > > > > > > so i need for example to find the culomn where "e" is sitting
> > > > > > >
> > > > > > > Dave Peterson ???:
> > > > > > > > =index(sheet2!a:a,match(A1,sheet2!b:b,0))
> > > > > > > >
> > > > > > > > Where sheet2 holds the table and A1 of the current sheet
(not sheet2) holds the
> > > > > > > > value to match (e in your example).
> > > > > > > >
> > > > > > > > You may want to read Debra Dalgleish's notes:
> > > > > > > > http://www.contextures.com/xlFunctions02.html (for
=vlookup())
> > > > > > > > and
> > > > > > > > http://www.contextures.com/xlFunctions03.html (for
=index(match()))
> > > > > > > >
> > > > > > > > yaniv.dg@gmail.com wrote:
> > > > > > > > >
> > > > > > > > > hi mark
> > > > > > > > > do you this there is a way to use the current functions
without vba
> > > > > > > > > becaouse the execute is coming from access
> > > > > > > > >
> > > > > > > > > my vlookup needs to be as folow
> > > > > > > > >
> > > > > > > > > for example:
> > > > > > > > > need to find 36 where the leter e is chosen that means:
> > > > > > > > >
> > > > > > > > > 36 a
> > > > > > > > > 36 b
> > > > > > > > > 36 c
> > > > > > > > > 36 e
> > > > > > > > >
> > > > > > > > > so the data willcome only from the row on 36 e but the
question how can
> > > > > > > > > we monitor both of the culomns in order to find the
correct row to use
> > > > > > > > >
> > > > > > > > > Mark Ivey ???:
> > > > > > > > > > Give this site a look over...
> > > > > > > > > >
> > > > > > > > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Mark Ivey
> > > > > > > > > > <yaniv.dg@gmail.com> wrote in message
news:1152345833.835018.327090@m73g2000cwd.googlegroups.com...
> > > > > > > > > > hi trying to vlookup on 2 culomns but im sure its not
the way
> > > > > > > > > > i need to compare 1 culomn find all the data there and
with this to
> > > > > > > > > > compare another data in another culomn based on this
2 to bring data
> > > > > > > > > > from third culomn
> > > > > > > > > > i cannot find a solution for this
> > > > > > > > > >
> > > > > > > > > > ------=_NextPart_000_0108_01C6A26A.2454AD00
> > > > > > > > > > Content-Type: text/html; charset=iso-8859-1
> > > > > > > > > > Content-Transfer-Encoding: quoted-printable
> > > > > > > > > > X-Google-AttachSize: 1197
> > > > > > > > > >
> > > > > > > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
Transitional//EN">
> > > > > > > > > > <HTML><HEAD>
> > > > > > > > > > <META http-equiv=Content-Type content="text/html;
charset=iso-8859-1">
> > > > > > > > > > <META content="MSHTML 6.00.5346.5" name=GENERATOR>
> > > > > > > > > > <STYLE></STYLE>
> > > > > > > > > > </HEAD>
> > > > > > > > > > <BODY bgColor=#ffffff>
> > > > > > > > > > <DIV>Give this site a look over...</DIV>
> > > > > > > > > > <DIV>&nbsp;</DIV>
> > > > > > > > > > <DIV><A
> > > > > > > > > >
href="http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www.bygsoftw
are.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > > > > > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > > > > > > > <BLOCKQUOTE
> > > > > > > > > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px;
MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > > > > > > > <DIV>&lt;<A
href="mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</A>&gt; wrote
> > > > > > > > > > in message <A
> > > > > > > > > >
href="news:1152345833.835018.327090@m73g2000cwd.googlegroups.com">news:11523
45833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> > > > > > > > > > trying to vlookup on 2 culomns but im sure its not the
way<BR>i need to
> > > > > > > > > > compare 1 culomn find all the data there and with this
to<BR>compare another
> > > > > > > > > > data in&nbsp; another culomn based on this 2 to bring
data<BR>from third
> > > > > > > > > > culomn<BR>i cannot find a solution for
this<BR></BLOCKQUOTE></BODY></HTML>
> > > > > > > > > >
> > > > > > > > > > ------=_NextPart_000_0108_01C6A26A.2454AD00--
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

[yaniv.dg@gmail.com]

yes i know,i tried this with ranging the culomns,it still doesnt work

Dave Peterson wrote:
> From Bob's first response:
>
> ...although I say column1 etc., but you cannot use a whole column, only p=
art of
> it.
>
>
>
> yaniv.dg@gmail.com wrote:
> >
> > =3DINDEX(C:C,MATCH(1,(H19=3DA:A)*(G19>=3DLEFT(B:B,1))*(G19<=3DRIGHT(B:B=
,1)),0))
> >
> > ok,maybe this can spread some light
> >
> > Dave Peterson =D7=9B=D7=AA=D7=91:
> > > Post the real formula that fails for you.
> > >
> > > yaniv.dg@gmail.com wrote:
> > > >
> > > > technically it should be something like this:
> > > > index(culomn3,match(1,(value=3Dculomn1)*(value>left(culomn2,1))*(va=
lue<right(culomn2,1)),0)
> > > > it gives #value or #n/a
> > > > (even after doinbg ctrl,shift,enter)
> > > >
> > > > but from some reason its not working
> > > >
> > > > yaniv.dg@gmail.com wrote:
> > > > > hi dave,
> > > > > i think this issue is consering excel becaouse i'm searching for =
the
> > > > > formula in excel,access is just the executer and thats it.
> > > > >
> > > > > i need to compare between 2 culomns in order to bring the correct=
data
> > > > > for example.
> > > > > 35....
> > > > > 36 1-5 a
> > > > > 36 5-7 b
> > > > > 36 9-18 c
> > > > > 36 20-25 d
> > > > > 37.....
> > > > >
> > > > > so for example i want to find 36 and the number c in order to bri=
ng the
> > > > > data from the third culomn
> > > > > so the result would be b(36 and 6 is in the range of 5 and 7)
> > > > > i can isulate the the ranges by left and right but from some reas=
on
> > > > > something is not working
> > > > >
> > > > > index/match working perfectly on specific data but when its conse=
ring
> > > > > ranges it might be diffrent
> > > > >
> > > > > Dave Peterson wrote:
> > > > > > I don't understand the question or the example and I've never u=
sed Access.
> > > > > >
> > > > > > Maybe someone else will jump in.
> > > > > >
> > > > > > yaniv.dg@gmail.com wrote:
> > > > > > >
> > > > > > > thank you very much for your help,it was very helpfull infora=
mtion that
> > > > > > > i used
> > > > > > >
> > > > > > > now it became little bit more complex becaouse i need to find=
data in
> > > > > > > once culomn and based on it to compare a range
> > > > > > > for example:
> > > > > > > 36-d a
> > > > > > > 36 d-f
> > > > > > > 36 k-r
> > > > > > > 36 s-t
> > > > > > >
> > > > > > > so i need for example to find the culomn where "e" is sitting
> > > > > > >
> > > > > > > Dave Peterson =D7=9B=D7=AA=D7=91:
> > > > > > > > =3Dindex(sheet2!a:a,match(A1,sheet2!b:b,0))
> > > > > > > >
> > > > > > > > Where sheet2 holds the table and A1 of the current sheet (n=
ot sheet2) holds the
> > > > > > > > value to match (e in your example).
> > > > > > > >
> > > > > > > > You may want to read Debra Dalgleish's notes:
> > > > > > > > http://www.contextures.com/xlFunctions02.html (for =3Dvloo=
kup())
> > > > > > > > and
> > > > > > > > http://www.contextures.com/xlFunctions03.html (for =3Dinde=
x(match()))
> > > > > > > >
> > > > > > > > yaniv.dg@gmail.com wrote:
> > > > > > > > >
> > > > > > > > > hi mark
> > > > > > > > > do you this there is a way to use the current functions w=
ithout vba
> > > > > > > > > becaouse the execute is coming from access
> > > > > > > > >
> > > > > > > > > my vlookup needs to be as folow
> > > > > > > > >
> > > > > > > > > for example:
> > > > > > > > > need to find 36 where the leter e is chosen that means:
> > > > > > > > >
> > > > > > > > > 36 a
> > > > > > > > > 36 b
> > > > > > > > > 36 c
> > > > > > > > > 36 e
> > > > > > > > >
> > > > > > > > > so the data willcome only from the row on 36 e but the qu=
estion how can
> > > > > > > > > we monitor both of the culomns in order to find the corre=
ct row to use
> > > > > > > > >
> > > > > > > > > Mark Ivey =D7=9B=D7=AA=D7=91:
> > > > > > > > > > Give this site a look over...
> > > > > > > > > >
> > > > > > > > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Mark Ivey
> > > > > > > > > > <yaniv.dg@gmail.com> wrote in message news:1152345833=
..835018.327090@m73g2000cwd.googlegroups.com...
> > > > > > > > > > hi trying to vlookup on 2 culomns but im sure its not=
the way
> > > > > > > > > > i need to compare 1 culomn find all the data there an=
d with this to
> > > > > > > > > > compare another data in another culomn based on this=
2 to bring data
> > > > > > > > > > from third culomn
> > > > > > > > > > i cannot find a solution for this
> > > > > > > > > >
> > > > > > > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00
> > > > > > > > > > Content-Type: text/html; charset=3Diso-8859-1
> > > > > > > > > > Content-Transfer-Encoding: quoted-printable
> > > > > > > > > > X-Google-AttachSize: 1197
> > > > > > > > > >
> > > > > > > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitiona=
l//EN">
> > > > > > > > > > <HTML><HEAD>
> > > > > > > > > > <META http-equiv=3DContent-Type content=3D"text/html; c=
harset=3Diso-8859-1">
> > > > > > > > > > <META content=3D"MSHTML 6.00.5346.5" name=3DGENERATOR>
> > > > > > > > > > <STYLE></STYLE>
> > > > > > > > > > </HEAD>
> > > > > > > > > > <BODY bgColor=3D#ffffff>
> > > > > > > > > > <DIV>Give this site a look over...</DIV>
> > > > > > > > > > <DIV>&nbsp;</DIV>
> > > > > > > > > > <DIV><A
> > > > > > > > > > href=3D"http://www.bygsoftware.com/Excel/VBA/vlookup2.h=
tm">http://www.bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > > > > > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > > > > > > > <BLOCKQUOTE
> > > > > > > > > > style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-=
LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > > > > > > > <DIV>&lt;<A href=3D"mailto:yaniv.dg@gmail.com">yaniv.=
dg@gmail.com</A>&gt; wrote
> > > > > > > > > > in message <A
> > > > > > > > > > href=3D"news:1152345833.835018.327090@m73g2000cwd.goo=
glegroups.com">news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</=
A>...</DIV>hi
> > > > > > > > > > trying to vlookup on 2 culomns but im sure its not th=
e way<BR>i need to
> > > > > > > > > > compare 1 culomn find all the data there and with thi=
s to<BR>compare another
> > > > > > > > > > data in&nbsp; another culomn based on this 2 to bring=
data<BR>from third
> > > > > > > > > > culomn<BR>i cannot find a solution for this<BR></BLOC=
KQUOTE></BODY></HTML>
> > > > > > > > > >
> > > > > > > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00--
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>=20
> --=20
>=20
> Dave Peterson

[Dave Peterson]

From Bob's first response:

....although I say column1 etc., but you cannot use a whole column, only part of
it.



yaniv.dg@gmail.com wrote:
>
> =INDEX(C:C,MATCH(1,(H19=A:A)*(G19>=LEFT(B:B,1))*(G19<=RIGHT(B:B,1)),0))
>
> ok,maybe this can spread some light
>
> Dave Peterson כתב:
> > Post the real formula that fails for you.
> >
> > yaniv.dg@gmail.com wrote:
> > >
> > > technically it should be something like this:
> > > index(culomn3,match(1,(value=culomn1)*(value>left(culomn2,1))*(value<right(culomn2,1)),0)
> > > it gives #value or #n/a
> > > (even after doinbg ctrl,shift,enter)
> > >
> > > but from some reason its not working
> > >
> > > yaniv.dg@gmail.com wrote:
> > > > hi dave,
> > > > i think this issue is consering excel becaouse i'm searching for the
> > > > formula in excel,access is just the executer and thats it.
> > > >
> > > > i need to compare between 2 culomns in order to bring the correct data
> > > > for example.
> > > > 35....
> > > > 36 1-5 a
> > > > 36 5-7 b
> > > > 36 9-18 c
> > > > 36 20-25 d
> > > > 37.....
> > > >
> > > > so for example i want to find 36 and the number c in order to bring the
> > > > data from the third culomn
> > > > so the result would be b(36 and 6 is in the range of 5 and 7)
> > > > i can isulate the the ranges by left and right but from some reason
> > > > something is not working
> > > >
> > > > index/match working perfectly on specific data but when its consering
> > > > ranges it might be diffrent
> > > >
> > > > Dave Peterson wrote:
> > > > > I don't understand the question or the example and I've never used Access.
> > > > >
> > > > > Maybe someone else will jump in.
> > > > >
> > > > > yaniv.dg@gmail.com wrote:
> > > > > >
> > > > > > thank you very much for your help,it was very helpfull inforamtion that
> > > > > > i used
> > > > > >
> > > > > > now it became little bit more complex becaouse i need to find data in
> > > > > > once culomn and based on it to compare a range
> > > > > > for example:
> > > > > > 36-d a
> > > > > > 36 d-f
> > > > > > 36 k-r
> > > > > > 36 s-t
> > > > > >
> > > > > > so i need for example to find the culomn where "e" is sitting
> > > > > >
> > > > > > Dave Peterson כתב:
> > > > > > > =index(sheet2!a:a,match(A1,sheet2!b:b,0))
> > > > > > >
> > > > > > > Where sheet2 holds the table and A1 of the current sheet (not sheet2) holds the
> > > > > > > value to match (e in your example).
> > > > > > >
> > > > > > > You may want to read Debra Dalgleish's notes:
> > > > > > > http://www.contextures.com/xlFunctions02.html (for =vlookup())
> > > > > > > and
> > > > > > > http://www.contextures.com/xlFunctions03.html (for =index(match()))
> > > > > > >
> > > > > > > yaniv.dg@gmail.com wrote:
> > > > > > > >
> > > > > > > > hi mark
> > > > > > > > do you this there is a way to use the current functions without vba
> > > > > > > > becaouse the execute is coming from access
> > > > > > > >
> > > > > > > > my vlookup needs to be as folow
> > > > > > > >
> > > > > > > > for example:
> > > > > > > > need to find 36 where the leter e is chosen that means:
> > > > > > > >
> > > > > > > > 36 a
> > > > > > > > 36 b
> > > > > > > > 36 c
> > > > > > > > 36 e
> > > > > > > >
> > > > > > > > so the data willcome only from the row on 36 e but the question how can
> > > > > > > > we monitor both of the culomns in order to find the correct row to use
> > > > > > > >
> > > > > > > > Mark Ivey כתב:
> > > > > > > > > Give this site a look over...
> > > > > > > > >
> > > > > > > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Mark Ivey
> > > > > > > > > <yaniv.dg@gmail.com> wrote in message news:1152345833.835018.327090@m73g2000cwd.googlegroups.com...
> > > > > > > > > hi trying to vlookup on 2 culomns but im sure its not the way
> > > > > > > > > i need to compare 1 culomn find all the data there and with this to
> > > > > > > > > compare another data in another culomn based on this 2 to bring data
> > > > > > > > > from third culomn
> > > > > > > > > i cannot find a solution for this
> > > > > > > > >
> > > > > > > > > ------=_NextPart_000_0108_01C6A26A.2454AD00
> > > > > > > > > Content-Type: text/html; charset=iso-8859-1
> > > > > > > > > Content-Transfer-Encoding: quoted-printable
> > > > > > > > > X-Google-AttachSize: 1197
> > > > > > > > >
> > > > > > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > > > > > > > <HTML><HEAD>
> > > > > > > > > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> > > > > > > > > <META content="MSHTML 6.00.5346.5" name=GENERATOR>
> > > > > > > > > <STYLE></STYLE>
> > > > > > > > > </HEAD>
> > > > > > > > > <BODY bgColor=#ffffff>
> > > > > > > > > <DIV>Give this site a look over...</DIV>
> > > > > > > > > <DIV>&nbsp;</DIV>
> > > > > > > > > <DIV><A
> > > > > > > > > href="http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www.bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > > > > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > > > > > > <BLOCKQUOTE
> > > > > > > > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > > > > > > <DIV>&lt;<A href="mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</A>&gt; wrote
> > > > > > > > > in message <A
> > > > > > > > > href="news:1152345833.835018.327090@m73g2000cwd.googlegroups.com">news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> > > > > > > > > trying to vlookup on 2 culomns but im sure its not the way<BR>i need to
> > > > > > > > > compare 1 culomn find all the data there and with this to<BR>compare another
> > > > > > > > > data in&nbsp; another culomn based on this 2 to bring data<BR>from third
> > > > > > > > > culomn<BR>i cannot find a solution for this<BR></BLOCKQUOTE></BODY></HTML>
> > > > > > > > >
> > > > > > > > > ------=_NextPart_000_0108_01C6A26A.2454AD00--
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[yaniv.dg@gmail.com]

=3DINDEX(C:C,MATCH(1,(H19=3DA:A)*(G19>=3DLEFT(B:B,1))*(G19<=3DRIGHT(B:B,1))=
,0))

ok,maybe this can spread some light

Dave Peterson =D7=9B=D7=AA=D7=91:
> Post the real formula that fails for you.
>
> yaniv.dg@gmail.com wrote:
> >
> > technically it should be something like this:
> > index(culomn3,match(1,(value=3Dculomn1)*(value>left(culomn2,1))*(value<=
right(culomn2,1)),0)
> > it gives #value or #n/a
> > (even after doinbg ctrl,shift,enter)
> >
> > but from some reason its not working
> >
> > yaniv.dg@gmail.com wrote:
> > > hi dave,
> > > i think this issue is consering excel becaouse i'm searching for the
> > > formula in excel,access is just the executer and thats it.
> > >
> > > i need to compare between 2 culomns in order to bring the correct data
> > > for example.
> > > 35....
> > > 36 1-5 a
> > > 36 5-7 b
> > > 36 9-18 c
> > > 36 20-25 d
> > > 37.....
> > >
> > > so for example i want to find 36 and the number c in order to bring t=
he
> > > data from the third culomn
> > > so the result would be b(36 and 6 is in the range of 5 and 7)
> > > i can isulate the the ranges by left and right but from some reason
> > > something is not working
> > >
> > > index/match working perfectly on specific data but when its consering
> > > ranges it might be diffrent
> > >
> > > Dave Peterson wrote:
> > > > I don't understand the question or the example and I've never used =
Access.
> > > >
> > > > Maybe someone else will jump in.
> > > >
> > > > yaniv.dg@gmail.com wrote:
> > > > >
> > > > > thank you very much for your help,it was very helpfull inforamtio=
n that
> > > > > i used
> > > > >
> > > > > now it became little bit more complex becaouse i need to find dat=
a in
> > > > > once culomn and based on it to compare a range
> > > > > for example:
> > > > > 36-d a
> > > > > 36 d-f
> > > > > 36 k-r
> > > > > 36 s-t
> > > > >
> > > > > so i need for example to find the culomn where "e" is sitting
> > > > >
> > > > > Dave Peterson =D7=9B=D7=AA=D7=91:
> > > > > > =3Dindex(sheet2!a:a,match(A1,sheet2!b:b,0))
> > > > > >
> > > > > > Where sheet2 holds the table and A1 of the current sheet (not s=
heet2) holds the
> > > > > > value to match (e in your example).
> > > > > >
> > > > > > You may want to read Debra Dalgleish's notes:
> > > > > > http://www.contextures.com/xlFunctions02.html (for =3Dvlookup(=
))
> > > > > > and
> > > > > > http://www.contextures.com/xlFunctions03.html (for =3Dindex(ma=
tch()))
> > > > > >
> > > > > > yaniv.dg@gmail.com wrote:
> > > > > > >
> > > > > > > hi mark
> > > > > > > do you this there is a way to use the current functions witho=
ut vba
> > > > > > > becaouse the execute is coming from access
> > > > > > >
> > > > > > > my vlookup needs to be as folow
> > > > > > >
> > > > > > > for example:
> > > > > > > need to find 36 where the leter e is chosen that means:
> > > > > > >
> > > > > > > 36 a
> > > > > > > 36 b
> > > > > > > 36 c
> > > > > > > 36 e
> > > > > > >
> > > > > > > so the data willcome only from the row on 36 e but the questi=
on how can
> > > > > > > we monitor both of the culomns in order to find the correct r=
ow to use
> > > > > > >
> > > > > > > Mark Ivey =D7=9B=D7=AA=D7=91:
> > > > > > > > Give this site a look over...
> > > > > > > >
> > > > > > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > > > > > >
> > > > > > > > --
> > > > > > > > Mark Ivey
> > > > > > > > <yaniv.dg@gmail.com> wrote in message news:1152345833.835=
018.327090@m73g2000cwd.googlegroups.com...
> > > > > > > > hi trying to vlookup on 2 culomns but im sure its not the=
way
> > > > > > > > i need to compare 1 culomn find all the data there and wi=
th this to
> > > > > > > > compare another data in another culomn based on this 2 t=
o bring data
> > > > > > > > from third culomn
> > > > > > > > i cannot find a solution for this
> > > > > > > >
> > > > > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00
> > > > > > > > Content-Type: text/html; charset=3Diso-8859-1
> > > > > > > > Content-Transfer-Encoding: quoted-printable
> > > > > > > > X-Google-AttachSize: 1197
> > > > > > > >
> > > > > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//E=
N">
> > > > > > > > <HTML><HEAD>
> > > > > > > > <META http-equiv=3DContent-Type content=3D"text/html; chars=
et=3Diso-8859-1">
> > > > > > > > <META content=3D"MSHTML 6.00.5346.5" name=3DGENERATOR>
> > > > > > > > <STYLE></STYLE>
> > > > > > > > </HEAD>
> > > > > > > > <BODY bgColor=3D#ffffff>
> > > > > > > > <DIV>Give this site a look over...</DIV>
> > > > > > > > <DIV>&nbsp;</DIV>
> > > > > > > > <DIV><A
> > > > > > > > href=3D"http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">=
http://www.bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > > > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > > > > > <BLOCKQUOTE
> > > > > > > > style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT=
: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > > > > > <DIV>&lt;<A href=3D"mailto:yaniv.dg@gmail.com">yaniv.dg@g=
mail.com</A>&gt; wrote
> > > > > > > > in message <A
> > > > > > > > href=3D"news:1152345833.835018.327090@m73g2000cwd.googleg=
roups.com">news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>..=
..</DIV>hi
> > > > > > > > trying to vlookup on 2 culomns but im sure its not the wa=
y<BR>i need to
> > > > > > > > compare 1 culomn find all the data there and with this to=
<BR>compare another
> > > > > > > > data in&nbsp; another culomn based on this 2 to bring dat=
a<BR>from third
> > > > > > > > culomn<BR>i cannot find a solution for this<BR></BLOCKQUO=
TE></BODY></HTML>
> > > > > > > >
> > > > > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00--
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
>=20
> --=20
>=20
> Dave Peterson

[Dave Peterson]

Post the real formula that fails for you.

yaniv.dg@gmail.com wrote:
>
> technically it should be something like this:
> index(culomn3,match(1,(value=culomn1)*(value>left(culomn2,1))*(value<right(culomn2,1)),0)
> it gives #value or #n/a
> (even after doinbg ctrl,shift,enter)
>
> but from some reason its not working
>
> yaniv.dg@gmail.com wrote:
> > hi dave,
> > i think this issue is consering excel becaouse i'm searching for the
> > formula in excel,access is just the executer and thats it.
> >
> > i need to compare between 2 culomns in order to bring the correct data
> > for example.
> > 35....
> > 36 1-5 a
> > 36 5-7 b
> > 36 9-18 c
> > 36 20-25 d
> > 37.....
> >
> > so for example i want to find 36 and the number c in order to bring the
> > data from the third culomn
> > so the result would be b(36 and 6 is in the range of 5 and 7)
> > i can isulate the the ranges by left and right but from some reason
> > something is not working
> >
> > index/match working perfectly on specific data but when its consering
> > ranges it might be diffrent
> >
> > Dave Peterson wrote:
> > > I don't understand the question or the example and I've never used Access.
> > >
> > > Maybe someone else will jump in.
> > >
> > > yaniv.dg@gmail.com wrote:
> > > >
> > > > thank you very much for your help,it was very helpfull inforamtion that
> > > > i used
> > > >
> > > > now it became little bit more complex becaouse i need to find data in
> > > > once culomn and based on it to compare a range
> > > > for example:
> > > > 36-d a
> > > > 36 d-f
> > > > 36 k-r
> > > > 36 s-t
> > > >
> > > > so i need for example to find the culomn where "e" is sitting
> > > >
> > > > Dave Peterson כתב:
> > > > > =index(sheet2!a:a,match(A1,sheet2!b:b,0))
> > > > >
> > > > > Where sheet2 holds the table and A1 of the current sheet (not sheet2) holds the
> > > > > value to match (e in your example).
> > > > >
> > > > > You may want to read Debra Dalgleish's notes:
> > > > > http://www.contextures.com/xlFunctions02.html (for =vlookup())
> > > > > and
> > > > > http://www.contextures.com/xlFunctions03.html (for =index(match()))
> > > > >
> > > > > yaniv.dg@gmail.com wrote:
> > > > > >
> > > > > > hi mark
> > > > > > do you this there is a way to use the current functions without vba
> > > > > > becaouse the execute is coming from access
> > > > > >
> > > > > > my vlookup needs to be as folow
> > > > > >
> > > > > > for example:
> > > > > > need to find 36 where the leter e is chosen that means:
> > > > > >
> > > > > > 36 a
> > > > > > 36 b
> > > > > > 36 c
> > > > > > 36 e
> > > > > >
> > > > > > so the data willcome only from the row on 36 e but the question how can
> > > > > > we monitor both of the culomns in order to find the correct row to use
> > > > > >
> > > > > > Mark Ivey כתב:
> > > > > > > Give this site a look over...
> > > > > > >
> > > > > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > > > > >
> > > > > > > --
> > > > > > > Mark Ivey
> > > > > > > <yaniv.dg@gmail.com> wrote in message news:1152345833.835018.327090@m73g2000cwd.googlegroups.com...
> > > > > > > hi trying to vlookup on 2 culomns but im sure its not the way
> > > > > > > i need to compare 1 culomn find all the data there and with this to
> > > > > > > compare another data in another culomn based on this 2 to bring data
> > > > > > > from third culomn
> > > > > > > i cannot find a solution for this
> > > > > > >
> > > > > > > ------=_NextPart_000_0108_01C6A26A.2454AD00
> > > > > > > Content-Type: text/html; charset=iso-8859-1
> > > > > > > Content-Transfer-Encoding: quoted-printable
> > > > > > > X-Google-AttachSize: 1197
> > > > > > >
> > > > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > > > > > <HTML><HEAD>
> > > > > > > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> > > > > > > <META content="MSHTML 6.00.5346.5" name=GENERATOR>
> > > > > > > <STYLE></STYLE>
> > > > > > > </HEAD>
> > > > > > > <BODY bgColor=#ffffff>
> > > > > > > <DIV>Give this site a look over...</DIV>
> > > > > > > <DIV>&nbsp;</DIV>
> > > > > > > <DIV><A
> > > > > > > href="http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www.bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > > > > <BLOCKQUOTE
> > > > > > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > > > > <DIV>&lt;<A href="mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</A>&gt; wrote
> > > > > > > in message <A
> > > > > > > href="news:1152345833.835018.327090@m73g2000cwd.googlegroups.com">news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> > > > > > > trying to vlookup on 2 culomns but im sure its not the way<BR>i need to
> > > > > > > compare 1 culomn find all the data there and with this to<BR>compare another
> > > > > > > data in&nbsp; another culomn based on this 2 to bring data<BR>from third
> > > > > > > culomn<BR>i cannot find a solution for this<BR></BLOCKQUOTE></BODY></HTML>
> > > > > > >
> > > > > > > ------=_NextPart_000_0108_01C6A26A.2454AD00--
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson

--

Dave Peterson

[yaniv.dg@gmail.com]

technically it should be something like this:
index(culomn3,match(1,(value=3Dculomn1)*(value>left(culomn2,1))*(value<righ=
t(culomn2,1)),0)
it gives #value or #n/a
(even after doinbg ctrl,shift,enter)

but from some reason its not working

yaniv.dg@gmail.com wrote:
> hi dave,
> i think this issue is consering excel becaouse i'm searching for the
> formula in excel,access is just the executer and thats it.
>
> i need to compare between 2 culomns in order to bring the correct data
> for example.
> 35....
> 36 1-5 a
> 36 5-7 b
> 36 9-18 c
> 36 20-25 d
> 37.....
>
> so for example i want to find 36 and the number c in order to bring the
> data from the third culomn
> so the result would be b(36 and 6 is in the range of 5 and 7)
> i can isulate the the ranges by left and right but from some reason
> something is not working
>
> index/match working perfectly on specific data but when its consering
> ranges it might be diffrent
>
> Dave Peterson wrote:
> > I don't understand the question or the example and I've never used Acce=
ss.
> >
> > Maybe someone else will jump in.
> >
> > yaniv.dg@gmail.com wrote:
> > >
> > > thank you very much for your help,it was very helpfull inforamtion th=
at
> > > i used
> > >
> > > now it became little bit more complex becaouse i need to find data in
> > > once culomn and based on it to compare a range
> > > for example:
> > > 36-d a
> > > 36 d-f
> > > 36 k-r
> > > 36 s-t
> > >
> > > so i need for example to find the culomn where "e" is sitting
> > >
> > > Dave Peterson =D7=9B=D7=AA=D7=91:
> > > > =3Dindex(sheet2!a:a,match(A1,sheet2!b:b,0))
> > > >
> > > > Where sheet2 holds the table and A1 of the current sheet (not sheet=
2) holds the
> > > > value to match (e in your example).
> > > >
> > > > You may want to read Debra Dalgleish's notes:
> > > > http://www.contextures.com/xlFunctions02.html (for =3Dvlookup())
> > > > and
> > > > http://www.contextures.com/xlFunctions03.html (for =3Dindex(match(=
)))
> > > >
> > > > yaniv.dg@gmail.com wrote:
> > > > >
> > > > > hi mark
> > > > > do you this there is a way to use the current functions without v=
ba
> > > > > becaouse the execute is coming from access
> > > > >
> > > > > my vlookup needs to be as folow
> > > > >
> > > > > for example:
> > > > > need to find 36 where the leter e is chosen that means:
> > > > >
> > > > > 36 a
> > > > > 36 b
> > > > > 36 c
> > > > > 36 e
> > > > >
> > > > > so the data willcome only from the row on 36 e but the question h=
ow can
> > > > > we monitor both of the culomns in order to find the correct row t=
o use
> > > > >
> > > > > Mark Ivey =D7=9B=D7=AA=D7=91:
> > > > > > Give this site a look over...
> > > > > >
> > > > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > > > >
> > > > > > --
> > > > > > Mark Ivey
> > > > > > <yaniv.dg@gmail.com> wrote in message news:1152345833.835018.=
327090@m73g2000cwd.googlegroups.com...
> > > > > > hi trying to vlookup on 2 culomns but im sure its not the way
> > > > > > i need to compare 1 culomn find all the data there and with t=
his to
> > > > > > compare another data in another culomn based on this 2 to br=
ing data
> > > > > > from third culomn
> > > > > > i cannot find a solution for this
> > > > > >
> > > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00
> > > > > > Content-Type: text/html; charset=3Diso-8859-1
> > > > > > Content-Transfer-Encoding: quoted-printable
> > > > > > X-Google-AttachSize: 1197
> > > > > >
> > > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > > > > <HTML><HEAD>
> > > > > > <META http-equiv=3DContent-Type content=3D"text/html; charset=
=3Diso-8859-1">
> > > > > > <META content=3D"MSHTML 6.00.5346.5" name=3DGENERATOR>
> > > > > > <STYLE></STYLE>
> > > > > > </HEAD>
> > > > > > <BODY bgColor=3D#ffffff>
> > > > > > <DIV>Give this site a look over...</DIV>
> > > > > > <DIV>&nbsp;</DIV>
> > > > > > <DIV><A
> > > > > > href=3D"http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http=
://www.bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > > > <BLOCKQUOTE
> > > > > > style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5p=
x; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > > > <DIV>&lt;<A href=3D"mailto:yaniv.dg@gmail.com">yaniv.dg@gmail=
..com</A>&gt; wrote
> > > > > > in message <A
> > > > > > href=3D"news:1152345833.835018.327090@m73g2000cwd.googlegroup=
s=2Ecom">news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>...<=
/DIV>hi
> > > > > > trying to vlookup on 2 culomns but im sure its not the way<BR=
>i need to
> > > > > > compare 1 culomn find all the data there and with this to<BR>=
compare another
> > > > > > data in&nbsp; another culomn based on this 2 to bring data<BR=
>from third
> > > > > > culomn<BR>i cannot find a solution for this<BR></BLOCKQUOTE><=
/BODY></HTML>
> > > > > >
> > > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00--
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >=20
> > --=20
> >=20
> > Dave Peterson

[Dave Peterson]

It looks like Bob Phillips' suggestion would work for you.

=index(b1:b99,match(1,(a1:a99=36)*(c1:c99="c"),0))

This is an array formula, though.

yaniv.dg@gmail.com wrote:
>
> hi dave,
> i think this issue is consering excel becaouse i'm searching for the
> formula in excel,access is just the executer and thats it.
>
> i need to compare between 2 culomns in order to bring the correct data
> for example.
> 35....
> 36 1-5 a
> 36 5-7 b
> 36 9-18 c
> 36 20-25 d
> 37.....
>
> so for example i want to find 36 and the number c in order to bring the
> data from the third culomn
> so the result would be b(36 and 6 is in the range of 5 and 7)
> i can isulate the the ranges by left and right but from some reason
> something is not working
>
> index/match working perfectly on specific data but when its consering
> ranges it might be diffrent
>
> Dave Peterson wrote:
> > I don't understand the question or the example and I've never used Access.
> >
> > Maybe someone else will jump in.
> >
> > yaniv.dg@gmail.com wrote:
> > >
> > > thank you very much for your help,it was very helpfull inforamtion that
> > > i used
> > >
> > > now it became little bit more complex becaouse i need to find data in
> > > once culomn and based on it to compare a range
> > > for example:
> > > 36-d a
> > > 36 d-f
> > > 36 k-r
> > > 36 s-t
> > >
> > > so i need for example to find the culomn where "e" is sitting
> > >
> > > Dave Peterson כתב:
> > > > =index(sheet2!a:a,match(A1,sheet2!b:b,0))
> > > >
> > > > Where sheet2 holds the table and A1 of the current sheet (not sheet2) holds the
> > > > value to match (e in your example).
> > > >
> > > > You may want to read Debra Dalgleish's notes:
> > > > http://www.contextures.com/xlFunctions02.html (for =vlookup())
> > > > and
> > > > http://www.contextures.com/xlFunctions03.html (for =index(match()))
> > > >
> > > > yaniv.dg@gmail.com wrote:
> > > > >
> > > > > hi mark
> > > > > do you this there is a way to use the current functions without vba
> > > > > becaouse the execute is coming from access
> > > > >
> > > > > my vlookup needs to be as folow
> > > > >
> > > > > for example:
> > > > > need to find 36 where the leter e is chosen that means:
> > > > >
> > > > > 36 a
> > > > > 36 b
> > > > > 36 c
> > > > > 36 e
> > > > >
> > > > > so the data willcome only from the row on 36 e but the question how can
> > > > > we monitor both of the culomns in order to find the correct row to use
> > > > >
> > > > > Mark Ivey כתב:
> > > > > > Give this site a look over...
> > > > > >
> > > > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > > > >
> > > > > > --
> > > > > > Mark Ivey
> > > > > > <yaniv.dg@gmail.com> wrote in message news:1152345833.835018.327090@m73g2000cwd.googlegroups.com...
> > > > > > hi trying to vlookup on 2 culomns but im sure its not the way
> > > > > > i need to compare 1 culomn find all the data there and with this to
> > > > > > compare another data in another culomn based on this 2 to bring data
> > > > > > from third culomn
> > > > > > i cannot find a solution for this
> > > > > >
> > > > > > ------=_NextPart_000_0108_01C6A26A.2454AD00
> > > > > > Content-Type: text/html; charset=iso-8859-1
> > > > > > Content-Transfer-Encoding: quoted-printable
> > > > > > X-Google-AttachSize: 1197
> > > > > >
> > > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > > > > <HTML><HEAD>
> > > > > > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> > > > > > <META content="MSHTML 6.00.5346.5" name=GENERATOR>
> > > > > > <STYLE></STYLE>
> > > > > > </HEAD>
> > > > > > <BODY bgColor=#ffffff>
> > > > > > <DIV>Give this site a look over...</DIV>
> > > > > > <DIV>&nbsp;</DIV>
> > > > > > <DIV><A
> > > > > > href="http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www.bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > > > <BLOCKQUOTE
> > > > > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > > > <DIV>&lt;<A href="mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</A>&gt; wrote
> > > > > > in message <A
> > > > > > href="news:1152345833.835018.327090@m73g2000cwd.googlegroups.com">news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> > > > > > trying to vlookup on 2 culomns but im sure its not the way<BR>i need to
> > > > > > compare 1 culomn find all the data there and with this to<BR>compare another
> > > > > > data in&nbsp; another culomn based on this 2 to bring data<BR>from third
> > > > > > culomn<BR>i cannot find a solution for this<BR></BLOCKQUOTE></BODY></HTML>
> > > > > >
> > > > > > ------=_NextPart_000_0108_01C6A26A.2454AD00--
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Dave Peterson]

I don't understand the question or the example and I've never used Access.

Maybe someone else will jump in.

yaniv.dg@gmail.com wrote:
>
> thank you very much for your help,it was very helpfull inforamtion that
> i used
>
> now it became little bit more complex becaouse i need to find data in
> once culomn and based on it to compare a range
> for example:
> 36-d a
> 36 d-f
> 36 k-r
> 36 s-t
>
> so i need for example to find the culomn where "e" is sitting
>
> Dave Peterson כתב:
> > =index(sheet2!a:a,match(A1,sheet2!b:b,0))
> >
> > Where sheet2 holds the table and A1 of the current sheet (not sheet2) holds the
> > value to match (e in your example).
> >
> > You may want to read Debra Dalgleish's notes:
> > http://www.contextures.com/xlFunctions02.html (for =vlookup())
> > and
> > http://www.contextures.com/xlFunctions03.html (for =index(match()))
> >
> > yaniv.dg@gmail.com wrote:
> > >
> > > hi mark
> > > do you this there is a way to use the current functions without vba
> > > becaouse the execute is coming from access
> > >
> > > my vlookup needs to be as folow
> > >
> > > for example:
> > > need to find 36 where the leter e is chosen that means:
> > >
> > > 36 a
> > > 36 b
> > > 36 c
> > > 36 e
> > >
> > > so the data willcome only from the row on 36 e but the question how can
> > > we monitor both of the culomns in order to find the correct row to use
> > >
> > > Mark Ivey כתב:
> > > > Give this site a look over...
> > > >
> > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > >
> > > > --
> > > > Mark Ivey
> > > > <yaniv.dg@gmail.com> wrote in message news:1152345833.835018.327090@m73g2000cwd.googlegroups.com...
> > > > hi trying to vlookup on 2 culomns but im sure its not the way
> > > > i need to compare 1 culomn find all the data there and with this to
> > > > compare another data in another culomn based on this 2 to bring data
> > > > from third culomn
> > > > i cannot find a solution for this
> > > >
> > > > ------=_NextPart_000_0108_01C6A26A.2454AD00
> > > > Content-Type: text/html; charset=iso-8859-1
> > > > Content-Transfer-Encoding: quoted-printable
> > > > X-Google-AttachSize: 1197
> > > >
> > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > > <HTML><HEAD>
> > > > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> > > > <META content="MSHTML 6.00.5346.5" name=GENERATOR>
> > > > <STYLE></STYLE>
> > > > </HEAD>
> > > > <BODY bgColor=#ffffff>
> > > > <DIV>Give this site a look over...</DIV>
> > > > <DIV>&nbsp;</DIV>
> > > > <DIV><A
> > > > href="http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www.bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > <BLOCKQUOTE
> > > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > <DIV>&lt;<A href="mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</A>&gt; wrote
> > > > in message <A
> > > > href="news:1152345833.835018.327090@m73g2000cwd.googlegroups.com">news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> > > > trying to vlookup on 2 culomns but im sure its not the way<BR>i need to
> > > > compare 1 culomn find all the data there and with this to<BR>compare another
> > > > data in&nbsp; another culomn based on this 2 to bring data<BR>from third
> > > > culomn<BR>i cannot find a solution for this<BR></BLOCKQUOTE></BODY></HTML>
> > > >
> > > > ------=_NextPart_000_0108_01C6A26A.2454AD00--
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[Dave Peterson]

This formula isn't an array formula and doesn't require you use
ctrl-shift-enter.

yaniv.dg@gmail.com wrote:
>
> hi,
> im almost close to a solution,
> my problem is the i my formula will be execute from access,
> how should i will implement enter+ctrl+shift when i'm doing automation?
>
> Dave Peterson wrote:
> > =index(sheet2!a:a,match(A1,sheet2!b:b,0))
> >
> > Where sheet2 holds the table and A1 of the current sheet (not sheet2) holds the
> > value to match (e in your example).
> >
> > You may want to read Debra Dalgleish's notes:
> > http://www.contextures.com/xlFunctions02.html (for =vlookup())
> > and
> > http://www.contextures.com/xlFunctions03.html (for =index(match()))
> >
> > yaniv.dg@gmail.com wrote:
> > >
> > > hi mark
> > > do you this there is a way to use the current functions without vba
> > > becaouse the execute is coming from access
> > >
> > > my vlookup needs to be as folow
> > >
> > > for example:
> > > need to find 36 where the leter e is chosen that means:
> > >
> > > 36 a
> > > 36 b
> > > 36 c
> > > 36 e
> > >
> > > so the data willcome only from the row on 36 e but the question how can
> > > we monitor both of the culomns in order to find the correct row to use
> > >
> > > Mark Ivey כתב:
> > > > Give this site a look over...
> > > >
> > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > >
> > > > --
> > > > Mark Ivey
> > > > <yaniv.dg@gmail.com> wrote in message news:1152345833.835018.327090@m73g2000cwd.googlegroups.com...
> > > > hi trying to vlookup on 2 culomns but im sure its not the way
> > > > i need to compare 1 culomn find all the data there and with this to
> > > > compare another data in another culomn based on this 2 to bring data
> > > > from third culomn
> > > > i cannot find a solution for this
> > > >
> > > > ------=_NextPart_000_0108_01C6A26A.2454AD00
> > > > Content-Type: text/html; charset=iso-8859-1
> > > > Content-Transfer-Encoding: quoted-printable
> > > > X-Google-AttachSize: 1197
> > > >
> > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > > <HTML><HEAD>
> > > > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> > > > <META content="MSHTML 6.00.5346.5" name=GENERATOR>
> > > > <STYLE></STYLE>
> > > > </HEAD>
> > > > <BODY bgColor=#ffffff>
> > > > <DIV>Give this site a look over...</DIV>
> > > > <DIV>&nbsp;</DIV>
> > > > <DIV><A
> > > > href="http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www.bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > <BLOCKQUOTE
> > > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > <DIV>&lt;<A href="mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</A>&gt; wrote
> > > > in message <A
> > > > href="news:1152345833.835018.327090@m73g2000cwd.googlegroups.com">news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> > > > trying to vlookup on 2 culomns but im sure its not the way<BR>i need to
> > > > compare 1 culomn find all the data there and with this to<BR>compare another
> > > > data in&nbsp; another culomn based on this 2 to bring data<BR>from third
> > > > culomn<BR>i cannot find a solution for this<BR></BLOCKQUOTE></BODY></HTML>
> > > >
> > > > ------=_NextPart_000_0108_01C6A26A.2454AD00--
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[yaniv.dg@gmail.com]

thank you very much for your help,it was very helpfull inforamtion that
i used

now it became little bit more complex becaouse i need to find data in
once culomn and based on it to compare a range
for example:
36-d a
36 d-f
36 k-r
36 s-t

so i need for example to find the culomn where "e" is sitting

Dave Peterson =D7=9B=D7=AA=D7=91:
> =3Dindex(sheet2!a:a,match(A1,sheet2!b:b,0))
>
> Where sheet2 holds the table and A1 of the current sheet (not sheet2) hol=
ds the
> value to match (e in your example).
>
> You may want to read Debra Dalgleish's notes:
> http://www.contextures.com/xlFunctions02.html (for =3Dvlookup())
> and
> http://www.contextures.com/xlFunctions03.html (for =3Dindex(match()))
>
> yaniv.dg@gmail.com wrote:
> >
> > hi mark
> > do you this there is a way to use the current functions without vba
> > becaouse the execute is coming from access
> >
> > my vlookup needs to be as folow
> >
> > for example:
> > need to find 36 where the leter e is chosen that means:
> >
> > 36 a
> > 36 b
> > 36 c
> > 36 e
> >
> > so the data willcome only from the row on 36 e but the question how can
> > we monitor both of the culomns in order to find the correct row to use
> >
> > Mark Ivey =D7=9B=D7=AA=D7=91:
> > > Give this site a look over...
> > >
> > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > >
> > > --
> > > Mark Ivey
> > > <yaniv.dg@gmail.com> wrote in message news:1152345833.835018.327090=
@m73g2000cwd.googlegroups.com...
> > > hi trying to vlookup on 2 culomns but im sure its not the way
> > > i need to compare 1 culomn find all the data there and with this to
> > > compare another data in another culomn based on this 2 to bring da=
ta
> > > from third culomn
> > > i cannot find a solution for this
> > >
> > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00
> > > Content-Type: text/html; charset=3Diso-8859-1
> > > Content-Transfer-Encoding: quoted-printable
> > > X-Google-AttachSize: 1197
> > >
> > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > <HTML><HEAD>
> > > <META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8=
859-1">
> > > <META content=3D"MSHTML 6.00.5346.5" name=3DGENERATOR>
> > > <STYLE></STYLE>
> > > </HEAD>
> > > <BODY bgColor=3D#ffffff>
> > > <DIV>Give this site a look over...</DIV>
> > > <DIV>&nbsp;</DIV>
> > > <DIV><A
> > > href=3D"http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www=
..bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > <BLOCKQUOTE
> > > style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BOR=
DER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > <DIV>&lt;<A href=3D"mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</=
A>&gt; wrote
> > > in message <A
> > > href=3D"news:1152345833.835018.327090@m73g2000cwd.googlegroups.com"=
>news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> > > trying to vlookup on 2 culomns but im sure its not the way<BR>i nee=
d to
> > > compare 1 culomn find all the data there and with this to<BR>compar=
e another
> > > data in&nbsp; another culomn based on this 2 to bring data<BR>from =
third
> > > culomn<BR>i cannot find a solution for this<BR></BLOCKQUOTE></BODY>=
</HTML>
> > >
> > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00--
>=20
> --=20
>=20
> Dave Peterson

[yaniv.dg@gmail.com]

hi,
im almost close to a solution,
my problem is the i my formula will be execute from access,
how should i will implement enter+ctrl+shift when i'm doing automation?


Dave Peterson wrote:
> =3Dindex(sheet2!a:a,match(A1,sheet2!b:b,0))
>
> Where sheet2 holds the table and A1 of the current sheet (not sheet2) hol=
ds the
> value to match (e in your example).
>
> You may want to read Debra Dalgleish's notes:
> http://www.contextures.com/xlFunctions02.html (for =3Dvlookup())
> and
> http://www.contextures.com/xlFunctions03.html (for =3Dindex(match()))
>
> yaniv.dg@gmail.com wrote:
> >
> > hi mark
> > do you this there is a way to use the current functions without vba
> > becaouse the execute is coming from access
> >
> > my vlookup needs to be as folow
> >
> > for example:
> > need to find 36 where the leter e is chosen that means:
> >
> > 36 a
> > 36 b
> > 36 c
> > 36 e
> >
> > so the data willcome only from the row on 36 e but the question how can
> > we monitor both of the culomns in order to find the correct row to use
> >
> > Mark Ivey =D7=9B=D7=AA=D7=91:
> > > Give this site a look over...
> > >
> > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > >
> > > --
> > > Mark Ivey
> > > <yaniv.dg@gmail.com> wrote in message news:1152345833.835018.327090=
@m73g2000cwd.googlegroups.com...
> > > hi trying to vlookup on 2 culomns but im sure its not the way
> > > i need to compare 1 culomn find all the data there and with this to
> > > compare another data in another culomn based on this 2 to bring da=
ta
> > > from third culomn
> > > i cannot find a solution for this
> > >
> > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00
> > > Content-Type: text/html; charset=3Diso-8859-1
> > > Content-Transfer-Encoding: quoted-printable
> > > X-Google-AttachSize: 1197
> > >
> > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > <HTML><HEAD>
> > > <META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8=
859-1">
> > > <META content=3D"MSHTML 6.00.5346.5" name=3DGENERATOR>
> > > <STYLE></STYLE>
> > > </HEAD>
> > > <BODY bgColor=3D#ffffff>
> > > <DIV>Give this site a look over...</DIV>
> > > <DIV>&nbsp;</DIV>
> > > <DIV><A
> > > href=3D"http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www=
..bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > <BLOCKQUOTE
> > > style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BOR=
DER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > <DIV>&lt;<A href=3D"mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</=
A>&gt; wrote
> > > in message <A
> > > href=3D"news:1152345833.835018.327090@m73g2000cwd.googlegroups.com"=
>news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> > > trying to vlookup on 2 culomns but im sure its not the way<BR>i nee=
d to
> > > compare 1 culomn find all the data there and with this to<BR>compar=
e another
> > > data in&nbsp; another culomn based on this 2 to bring data<BR>from =
third
> > > culomn<BR>i cannot find a solution for this<BR></BLOCKQUOTE></BODY>=
</HTML>
> > >
> > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00--
>=20
> --=20
>=20
> Dave Peterson

[Dave Peterson]

=index(sheet2!a:a,match(A1,sheet2!b:b,0))

Where sheet2 holds the table and A1 of the current sheet (not sheet2) holds the
value to match (e in your example).

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

yaniv.dg@gmail.com wrote:
>
> hi mark
> do you this there is a way to use the current functions without vba
> becaouse the execute is coming from access
>
> my vlookup needs to be as folow
>
> for example:
> need to find 36 where the leter e is chosen that means:
>
> 36 a
> 36 b
> 36 c
> 36 e
>
> so the data willcome only from the row on 36 e but the question how can
> we monitor both of the culomns in order to find the correct row to use
>
> Mark Ivey כתב:
> > Give this site a look over...
> >
> > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> >
> > --
> > Mark Ivey
> > <yaniv.dg@gmail.com> wrote in message news:1152345833.835018.327090@m73g2000cwd.googlegroups.com...
> > hi trying to vlookup on 2 culomns but im sure its not the way
> > i need to compare 1 culomn find all the data there and with this to
> > compare another data in another culomn based on this 2 to bring data
> > from third culomn
> > i cannot find a solution for this
> >
> > ------=_NextPart_000_0108_01C6A26A.2454AD00
> > Content-Type: text/html; charset=iso-8859-1
> > Content-Transfer-Encoding: quoted-printable
> > X-Google-AttachSize: 1197
> >
> > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > <HTML><HEAD>
> > <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> > <META content="MSHTML 6.00.5346.5" name=GENERATOR>
> > <STYLE></STYLE>
> > </HEAD>
> > <BODY bgColor=#ffffff>
> > <DIV>Give this site a look over...</DIV>
> > <DIV>&nbsp;</DIV>
> > <DIV><A
> > href="http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www.bygsoftware.com/Excel/VBA/vlookup2.htm</A></DIV>
> > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > <BLOCKQUOTE
> > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > <DIV>&lt;<A href="mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</A>&gt; wrote
> > in message <A
> > href="news:1152345833.835018.327090@m73g2000cwd.googlegroups.com">news:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> > trying to vlookup on 2 culomns but im sure its not the way<BR>i need to
> > compare 1 culomn find all the data there and with this to<BR>compare another
> > data in&nbsp; another culomn based on this 2 to bring data<BR>from third
> > culomn<BR>i cannot find a solution for this<BR></BLOCKQUOTE></BODY></HTML>
> >
> > ------=_NextPart_000_0108_01C6A26A.2454AD00--

--

Dave Peterson

[yaniv.dg@gmail.com]

hi mark
do you this there is a way to use the current functions without vba
becaouse the execute is coming from access

my vlookup needs to be as folow

for example:
need to find 36 where the leter e is chosen that means:


36 a
36 b
36 c
36 e

so the data willcome only from the row on 36 e but the question how can
we monitor both of the culomns in order to find the correct row to use


Mark Ivey =D7=9B=D7=AA=D7=91:
> Give this site a look over...
>
> http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
>
> --
> Mark Ivey
> <yaniv.dg@gmail.com> wrote in message news:1152345833.835018.327090@m73=
g2000cwd.googlegroups.com...
> hi trying to vlookup on 2 culomns but im sure its not the way
> i need to compare 1 culomn find all the data there and with this to
> compare another data in another culomn based on this 2 to bring data
> from third culomn
> i cannot find a solution for this
>
> ------=3D_NextPart_000_0108_01C6A26A.2454AD00
> Content-Type: text/html; charset=3Diso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 1197
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-=
1">
> <META content=3D"MSHTML 6.00.5346.5" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV>Give this site a look over...</DIV>
> <DIV>&nbsp;</DIV>
> <DIV><A
> href=3D"http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www.byg=
software.com/Excel/VBA/vlookup2.htm</A></DIV>
> <DIV><BR>-- <BR>Mark Ivey</DIV>
> <BLOCKQUOTE
> style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-=
LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> <DIV>&lt;<A href=3D"mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</A>&g=
t; wrote
> in message <A
> href=3D"news:1152345833.835018.327090@m73g2000cwd.googlegroups.com">new=
s:1152345833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> trying to vlookup on 2 culomns but im sure its not the way<BR>i need to
> compare 1 culomn find all the data there and with this to<BR>compare an=
other
> data in&nbsp; another culomn based on this 2 to bring data<BR>from third
> culomn<BR>i cannot find a solution for this<BR></BLOCKQUOTE></BODY></HT=
ML>
>=20
> ------=3D_NextPart_000_0108_01C6A26A.2454AD00--

[yaniv.dg@gmail.com]

hi bob,
i think i need to divide the data to 2 culomns and thats it becaouse it
seems like when i'm trying to use left and right in an array it doesnt
not work

Bob Phillips =D7=9B=D7=AA=D7=91:
> So you tried
>
> =3DINDEX(C2:C200,MATCH(1,(H19=3DA2:A200)*(G19>=3DLEFT(B2:B200,1))*(G19<=
=3DRIGHT(B2:B
> 200,1)),0))
>
> did you, and you array entered it?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> <yaniv.dg@gmail.com> wrote in message
> news:1152512117.911223.133660@75g2000cwc.googlegroups.com...
> yes i know,i tried this with ranging the culomns,it still doesnt work
>
> Dave Peterson wrote:
> > From Bob's first response:
> >
> > ...although I say column1 etc., but you cannot use a whole column, only
> part of
> > it.
> >
> >
> >
> > yaniv.dg@gmail.com wrote:
> > >
> > > =3DINDEX(C:C,MATCH(1,(H19=3DA:A)*(G19>=3DLEFT(B:B,1))*(G19<=3DRIGHT(B=
:B,1)),0))
> > >
> > > ok,maybe this can spread some light
> > >
> > > Dave Peterson ???:
> > > > Post the real formula that fails for you.
> > > >
> > > > yaniv.dg@gmail.com wrote:
> > > > >
> > > > > technically it should be something like this:
> > > > >
> index(culomn3,match(1,(value=3Dculomn1)*(value>left(culomn2,1))*(value<ri=
ght(c
> ulomn2,1)),0)
> > > > > it gives #value or #n/a
> > > > > (even after doinbg ctrl,shift,enter)
> > > > >
> > > > > but from some reason its not working
> > > > >
> > > > > yaniv.dg@gmail.com wrote:
> > > > > > hi dave,
> > > > > > i think this issue is consering excel becaouse i'm searching for
> the
> > > > > > formula in excel,access is just the executer and thats it.
> > > > > >
> > > > > > i need to compare between 2 culomns in order to bring the corre=
ct
> data
> > > > > > for example.
> > > > > > 35....
> > > > > > 36 1-5 a
> > > > > > 36 5-7 b
> > > > > > 36 9-18 c
> > > > > > 36 20-25 d
> > > > > > 37.....
> > > > > >
> > > > > > so for example i want to find 36 and the number c in order to
> bring the
> > > > > > data from the third culomn
> > > > > > so the result would be b(36 and 6 is in the range of 5 and 7)
> > > > > > i can isulate the the ranges by left and right but from some
> reason
> > > > > > something is not working
> > > > > >
> > > > > > index/match working perfectly on specific data but when its
> consering
> > > > > > ranges it might be diffrent
> > > > > >
> > > > > > Dave Peterson wrote:
> > > > > > > I don't understand the question or the example and I've never
> used Access.
> > > > > > >
> > > > > > > Maybe someone else will jump in.
> > > > > > >
> > > > > > > yaniv.dg@gmail.com wrote:
> > > > > > > >
> > > > > > > > thank you very much for your help,it was very helpfull
> inforamtion that
> > > > > > > > i used
> > > > > > > >
> > > > > > > > now it became little bit more complex becaouse i need to fi=
nd
> data in
> > > > > > > > once culomn and based on it to compare a range
> > > > > > > > for example:
> > > > > > > > 36-d a
> > > > > > > > 36 d-f
> > > > > > > > 36 k-r
> > > > > > > > 36 s-t
> > > > > > > >
> > > > > > > > so i need for example to find the culomn where "e" is sitti=
ng
> > > > > > > >
> > > > > > > > Dave Peterson ???:
> > > > > > > > > =3Dindex(sheet2!a:a,match(A1,sheet2!b:b,0))
> > > > > > > > >
> > > > > > > > > Where sheet2 holds the table and A1 of the current sheet
> (not sheet2) holds the
> > > > > > > > > value to match (e in your example).
> > > > > > > > >
> > > > > > > > > You may want to read Debra Dalgleish's notes:
> > > > > > > > > http://www.contextures.com/xlFunctions02.html (for
> =3Dvlookup())
> > > > > > > > > and
> > > > > > > > > http://www.contextures.com/xlFunctions03.html (for
> =3Dindex(match()))
> > > > > > > > >
> > > > > > > > > yaniv.dg@gmail.com wrote:
> > > > > > > > > >
> > > > > > > > > > hi mark
> > > > > > > > > > do you this there is a way to use the current functions
> without vba
> > > > > > > > > > becaouse the execute is coming from access
> > > > > > > > > >
> > > > > > > > > > my vlookup needs to be as folow
> > > > > > > > > >
> > > > > > > > > > for example:
> > > > > > > > > > need to find 36 where the leter e is chosen that means:
> > > > > > > > > >
> > > > > > > > > > 36 a
> > > > > > > > > > 36 b
> > > > > > > > > > 36 c
> > > > > > > > > > 36 e
> > > > > > > > > >
> > > > > > > > > > so the data willcome only from the row on 36 e but the
> question how can
> > > > > > > > > > we monitor both of the culomns in order to find the
> correct row to use
> > > > > > > > > >
> > > > > > > > > > Mark Ivey ???:
> > > > > > > > > > > Give this site a look over...
> > > > > > > > > > >
> > > > > > > > > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > > Mark Ivey
> > > > > > > > > > > <yaniv.dg@gmail.com> wrote in message
> news:1152345833.835018.327090@m73g2000cwd.googlegroups.com...
> > > > > > > > > > > hi trying to vlookup on 2 culomns but im sure its n=
ot
> the way
> > > > > > > > > > > i need to compare 1 culomn find all the data there =
and
> with this to
> > > > > > > > > > > compare another data in another culomn based on th=
is
> 2 to bring data
> > > > > > > > > > > from third culomn
> > > > > > > > > > > i cannot find a solution for this
> > > > > > > > > > >
> > > > > > > > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00
> > > > > > > > > > > Content-Type: text/html; charset=3Diso-8859-1
> > > > > > > > > > > Content-Transfer-Encoding: quoted-printable
> > > > > > > > > > > X-Google-AttachSize: 1197
> > > > > > > > > > >
> > > > > > > > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
> Transitional//EN">
> > > > > > > > > > > <HTML><HEAD>
> > > > > > > > > > > <META http-equiv=3DContent-Type content=3D"text/html;
> charset=3Diso-8859-1">
> > > > > > > > > > > <META content=3D"MSHTML 6.00.5346.5" name=3DGENERATOR>
> > > > > > > > > > > <STYLE></STYLE>
> > > > > > > > > > > </HEAD>
> > > > > > > > > > > <BODY bgColor=3D#ffffff>
> > > > > > > > > > > <DIV>Give this site a look over...</DIV>
> > > > > > > > > > > <DIV>&nbsp;</DIV>
> > > > > > > > > > > <DIV><A
> > > > > > > > > > >
> href=3D"http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www.byg=
softw
> are.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > > > > > > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > > > > > > > > <BLOCKQUOTE
> > > > > > > > > > > style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px;
> MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > > > > > > > > <DIV>&lt;<A
> href=3D"mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</A>&gt; wrote
> > > > > > > > > > > in message <A
> > > > > > > > > > >
> href=3D"news:1152345833.835018.327090@m73g2000cwd.googlegroups.com">news:=
11523
> 45833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> > > > > > > > > > > trying to vlookup on 2 culomns but im sure its not =
the
> way<BR>i need to
> > > > > > > > > > > compare 1 culomn find all the data there and with t=
his
> to<BR>compare another
> > > > > > > > > > > data in&nbsp; another culomn based on this 2 to bri=
ng
> data<BR>from third
> > > > > > > > > > > culomn<BR>i cannot find a solution for
> this<BR></BLOCKQUOTE></BODY></HTML>
> > > > > > > > > > >
> > > > > > > > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00--
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > Dave Peterson
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --=20
> >
> > Dave Peterson

[yaniv.dg@gmail.com]

hi bob,
i have some problem in the formula that i showed you,from some reason
H19(see below) is compared to TRUE and FALSE and H19,G19 compared to
1,so when i'm matching between the two i have all the time error in
vba,
in the excel formula online its ok,i'm comparing all for them to 1 but
in the VBA code it cannot be matched together,
do you have a solution for me?

yaniv.dg@gmail.com wrote:
> hi bob,
> i think i need to divide the data to 2 culomns and thats it becaouse it
> seems like when i'm trying to use left and right in an array it doesnt
> not work
>
> Bob Phillips =D7=9B=D7=AA=D7=91:
> > So you tried
> >
> > =3DINDEX(C2:C200,MATCH(1,(H19=3DA2:A200)*(G19>=3DLEFT(B2:B200,1))*(G19<=
=3DRIGHT(B2:B
> > 200,1)),0))
> >
> > did you, and you array entered it?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > <yaniv.dg@gmail.com> wrote in message
> > news:1152512117.911223.133660@75g2000cwc.googlegroups.com...
> > yes i know,i tried this with ranging the culomns,it still doesnt work
> >
> > Dave Peterson wrote:
> > > From Bob's first response:
> > >
> > > ...although I say column1 etc., but you cannot use a whole column, on=
ly
> > part of
> > > it.
> > >
> > >
> > >
> > > yaniv.dg@gmail.com wrote:
> > > >
> > > > =3DINDEX(C:C,MATCH(1,(H19=3DA:A)*(G19>=3DLEFT(B:B,1))*(G19<=3DRIGHT=
(B:B,1)),0))
> > > >
> > > > ok,maybe this can spread some light
> > > >
> > > > Dave Peterson ???:
> > > > > Post the real formula that fails for you.
> > > > >
> > > > > yaniv.dg@gmail.com wrote:
> > > > > >
> > > > > > technically it should be something like this:
> > > > > >
> > index(culomn3,match(1,(value=3Dculomn1)*(value>left(culomn2,1))*(value<=
right(c
> > ulomn2,1)),0)
> > > > > > it gives #value or #n/a
> > > > > > (even after doinbg ctrl,shift,enter)
> > > > > >
> > > > > > but from some reason its not working
> > > > > >
> > > > > > yaniv.dg@gmail.com wrote:
> > > > > > > hi dave,
> > > > > > > i think this issue is consering excel becaouse i'm searching =
for
> > the
> > > > > > > formula in excel,access is just the executer and thats it.
> > > > > > >
> > > > > > > i need to compare between 2 culomns in order to bring the cor=
rect
> > data
> > > > > > > for example.
> > > > > > > 35....
> > > > > > > 36 1-5 a
> > > > > > > 36 5-7 b
> > > > > > > 36 9-18 c
> > > > > > > 36 20-25 d
> > > > > > > 37.....
> > > > > > >
> > > > > > > so for example i want to find 36 and the number c in order to
> > bring the
> > > > > > > data from the third culomn
> > > > > > > so the result would be b(36 and 6 is in the range of 5 and 7)
> > > > > > > i can isulate the the ranges by left and right but from some
> > reason
> > > > > > > something is not working
> > > > > > >
> > > > > > > index/match working perfectly on specific data but when its
> > consering
> > > > > > > ranges it might be diffrent
> > > > > > >
> > > > > > > Dave Peterson wrote:
> > > > > > > > I don't understand the question or the example and I've nev=
er
> > used Access.
> > > > > > > >
> > > > > > > > Maybe someone else will jump in.
> > > > > > > >
> > > > > > > > yaniv.dg@gmail.com wrote:
> > > > > > > > >
> > > > > > > > > thank you very much for your help,it was very helpfull
> > inforamtion that
> > > > > > > > > i used
> > > > > > > > >
> > > > > > > > > now it became little bit more complex becaouse i need to =
find
> > data in
> > > > > > > > > once culomn and based on it to compare a range
> > > > > > > > > for example:
> > > > > > > > > 36-d a
> > > > > > > > > 36 d-f
> > > > > > > > > 36 k-r
> > > > > > > > > 36 s-t
> > > > > > > > >
> > > > > > > > > so i need for example to find the culomn where "e" is sit=
ting
> > > > > > > > >
> > > > > > > > > Dave Peterson ???:
> > > > > > > > > > =3Dindex(sheet2!a:a,match(A1,sheet2!b:b,0))
> > > > > > > > > >
> > > > > > > > > > Where sheet2 holds the table and A1 of the current sheet
> > (not sheet2) holds the
> > > > > > > > > > value to match (e in your example).
> > > > > > > > > >
> > > > > > > > > > You may want to read Debra Dalgleish's notes:
> > > > > > > > > > http://www.contextures.com/xlFunctions02.html (for
> > =3Dvlookup())
> > > > > > > > > > and
> > > > > > > > > > http://www.contextures.com/xlFunctions03.html (for
> > =3Dindex(match()))
> > > > > > > > > >
> > > > > > > > > > yaniv.dg@gmail.com wrote:
> > > > > > > > > > >
> > > > > > > > > > > hi mark
> > > > > > > > > > > do you this there is a way to use the current functio=
ns
> > without vba
> > > > > > > > > > > becaouse the execute is coming from access
> > > > > > > > > > >
> > > > > > > > > > > my vlookup needs to be as folow
> > > > > > > > > > >
> > > > > > > > > > > for example:
> > > > > > > > > > > need to find 36 where the leter e is chosen that mean=
s:
> > > > > > > > > > >
> > > > > > > > > > > 36 a
> > > > > > > > > > > 36 b
> > > > > > > > > > > 36 c
> > > > > > > > > > > 36 e
> > > > > > > > > > >
> > > > > > > > > > > so the data willcome only from the row on 36 e but the
> > question how can
> > > > > > > > > > > we monitor both of the culomns in order to find the
> > correct row to use
> > > > > > > > > > >
> > > > > > > > > > > Mark Ivey ???:
> > > > > > > > > > > > Give this site a look over...
> > > > > > > > > > > >
> > > > > > > > > > > > http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
> > > > > > > > > > > >
> > > > > > > > > > > > --
> > > > > > > > > > > > Mark Ivey
> > > > > > > > > > > > <yaniv.dg@gmail.com> wrote in message
> > news:1152345833.835018.327090@m73g2000cwd.googlegroups.com...
> > > > > > > > > > > > hi trying to vlookup on 2 culomns but im sure its=
not
> > the way
> > > > > > > > > > > > i need to compare 1 culomn find all the data ther=
e and
> > with this to
> > > > > > > > > > > > compare another data in another culomn based on =
this
> > 2 to bring data
> > > > > > > > > > > > from third culomn
> > > > > > > > > > > > i cannot find a solution for this
> > > > > > > > > > > >
> > > > > > > > > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00
> > > > > > > > > > > > Content-Type: text/html; charset=3Diso-8859-1
> > > > > > > > > > > > Content-Transfer-Encoding: quoted-printable
> > > > > > > > > > > > X-Google-AttachSize: 1197
> > > > > > > > > > > >
> > > > > > > > > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
> > Transitional//EN">
> > > > > > > > > > > > <HTML><HEAD>
> > > > > > > > > > > > <META http-equiv=3DContent-Type content=3D"text/htm=
l;
> > charset=3Diso-8859-1">
> > > > > > > > > > > > <META content=3D"MSHTML 6.00.5346.5" name=3DGENERAT=
OR>
> > > > > > > > > > > > <STYLE></STYLE>
> > > > > > > > > > > > </HEAD>
> > > > > > > > > > > > <BODY bgColor=3D#ffffff>
> > > > > > > > > > > > <DIV>Give this site a look over...</DIV>
> > > > > > > > > > > > <DIV>&nbsp;</DIV>
> > > > > > > > > > > > <DIV><A
> > > > > > > > > > > >
> > href=3D"http://www.bygsoftware.com/Excel/VBA/vlookup2.htm">http://www.b=
ygsoftw
> > are.com/Excel/VBA/vlookup2.htm</A></DIV>
> > > > > > > > > > > > <DIV><BR>-- <BR>Mark Ivey</DIV>
> > > > > > > > > > > > <BLOCKQUOTE
> > > > > > > > > > > > style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px;
> > MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
> > > > > > > > > > > > <DIV>&lt;<A
> > href=3D"mailto:yaniv.dg@gmail.com">yaniv.dg@gmail.com</A>&gt; wrote
> > > > > > > > > > > > in message <A
> > > > > > > > > > > >
> > href=3D"news:1152345833.835018.327090@m73g2000cwd.googlegroups.com">new=
s:11523
> > 45833.835018.327090@m73g2000cwd.googlegroups.com</A>...</DIV>hi
> > > > > > > > > > > > trying to vlookup on 2 culomns but im sure its no=
t the
> > way<BR>i need to
> > > > > > > > > > > > compare 1 culomn find all the data there and with=
this
> > to<BR>compare another
> > > > > > > > > > > > data in&nbsp; another culomn based on this 2 to b=
ring
> > data<BR>from third
> > > > > > > > > > > > culomn<BR>i cannot find a solution for
> > this<BR></BLOCKQUOTE></BODY></HTML>
> > > > > > > > > > > >
> > > > > > > > > > > > ------=3D_NextPart_000_0108_01C6A26A.2454AD00--
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > Dave Peterson
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --=20
> > >
> > > Dave Peterson