vlookup on 2 columns

  • Thread starter Thread starter yaniv.dg
  • Start date Start date
Y

yaniv.dg

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
 
=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)
 
Give this site a look over...

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

--
Mark Ivey
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
 
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 כתב:
 
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?
 
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 כתב:
 
This formula isn't an array formula and doesn't require you use
ctrl-shift-enter.
 
I don't understand the question or the example and I've never used Access.

Maybe someone else will jump in.
 
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
 
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.

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
Click to expand...
 
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

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
Click to expand...
 
Post the real formula that fails for you.

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
Click to expand...
 
=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 כתב:
 
From Bob's first response:

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



=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 כתב:
Click to expand...
 
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)

yes i know,i tried this with ranging the culomns,it still doesnt work
 
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 כתב:
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)

yes i know,i tried this with ranging the culomns,it still doesnt work
Click to expand...
 
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?
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

controling the spread of fields in the excel 5
aggregation problem 4
in an Excel database, comparaison between two columns with "almost"the same names => how to search i 1
Vlookup or similar 7
Formula not working - vlookup 1
Can't Explain N/A in Vlookup 2
VLookup Function HELP 7
1 VLOOKUP question 3

Back
Top