V Lookup

[nedy_03]

Hello,

Could you please help me on this!
I have 3 columns: in column B telephone numbers , in C notes about the numbers from B, in D a range a numbers that contains those from B too ...
In column A I need a formula that search in D the number from B and when it finds it, gives the note from C, corisponding to that certain number.

Please give a look to the comment from cell A622 to understand better! :-S

[nedy_03]

To indentify where the duplicates are I used in B2 the formula =IF(ISNUMBER(MATCH(D2,$B$2:$B$621,0)),"ok",""), and pulled in down;
but instead of "ok" I want the corispondig note from C2 ...

Please give a look to the A2'comment ...

[Bob Phillips]

=IF(INDEX(C:C,MATCH(B2,D:D,0))=0,"",INDEX(C:C,MATCH(B2,D:D,0)))

[Simon Lloyd]

See attached, i have put a VlookUp in column A that looks for a match to the value of coulm d in column a and displays the results from c

Regards,
Simon

[Simon Lloyd]

Bob, wouldn't the simple Vlookup do?, or have i misread what he is asking for?

Regards,
Simon

[nedy_03]

See attached, i have put a VlookUp in column A that looks for a match to the value of coulm d in column a and displays the results from c

Regards,
Simon

In column A I should have only 620 comments from C ... for every number from that is in B too I should get the comment form C ...

[nedy_03]

=IF(INDEX(C:C,MATCH(B2,D:D,0))=0,"",INDEX(C:C,MATCH(B2,D:D,0)))

It partialy works .. in A 3075 ,by ex, I shold get the C12 commnet (User busy) ... because the B12 number we can find it in D 3075 too ..

[Simon Lloyd]

paste this in to A2 and copy down!
=VLOOKUP($D2,$B2:$C$5919,2)

Regards,
SImon

[Bob Phillips]

=IF(ISNA(INDEX(C:C,MATCH(D2,B:B,0))),"",INDEX(C:C,MATCH(D2,B:B,0)))

[Simon Lloyd]

Just been messing around trying to get rid of the #N/A enter this in to A2 and copy down
=IF(ISNA(VLOOKUP($D2,$B$2:$D$5919,2, FALSE)),"",(VLOOKUP($D2,$B$2:$D$5919,2, FALSE)))

Regards,
Simon

[joki]

See attached, i have put a VlookUp in column A that looks for a match to the value of coulm d in column a and displays the results from c

Regards,
Simon

[nedy_03]

=IF(ISNA(INDEX(C:C,MATCH(D2,B:B,0))),"",INDEX(C:C,MATCH(D2,B:B,0)))

It perfectly works ..thx

[nedy_03]

Question! :D .. ISNA function is somethimg like ISNUMBER ??? ... What does it do ? ...

[nedy_03]

Another question! .. why is it necessary to put the formula twice? : =IF(ISNA(INDEX(H:H,MATCH(I1,G:G,0))),"",INDEX(H:H,MATCH(I1,G:G,0))) ...
Could u explain me pls how itworks ? .. :old:

[Bob Phillips]

ISNA is an error checking function. The VLOOKUP may not find a match, in whih case it returns #N/A. ISNA tests that, so you use th VLLOOKUP once and test the result using ISNA, if that is trfue (i.e. no match), you set the formula result to "", else to the VLOOKUP result

=IF(lookup_formula_fails,"",lookup_formula)

[Simon Lloyd]

A function would return the value #N/A if no value is found, the use of ISNA provides a way of showing the cell to be empty if no match is found!

The formula is put in once for the ISNA value then a "," to move to the next part of the IF statement you then have double quotes to display a blank if the formula does not return a value then "," again to move on to the last part of the IF statement.

Regards,
Simon

[Simon Lloyd]

Ooops!, sorry Bob i wasn't trying to step on your toes!, just took me a little longer to explain it than you and in a less professional way.

Regards,
Simon

[Bob Phillips]

Ooops!, sorry Bob i wasn't trying to step on your toes!, just took me a little longer to explain it than you and in a less professional way.

No issue, with two perspectives, nedy_03 has a better chance of understanding. In the newsgroups we often get a situation where a poster gets 5 or 6 responses, often identical.

[Simon Lloyd]

i suppose your right, but when i posted and then saw yours it kinda looked to me as though i was trying to out do you, impossible of course!

Regards,
Lowly Pupil

[Bob Phillips]

I think the poster should be happy that two people are trying to help him.

[nedy_03]

Yap, the poster is very happy and gratefull .. Thx guys!

Nedy_03

[apple]

Hi,

Can anyone show me as file that i attach.

Please do see my explanation as below:

1. Sheet 1 name "find" tab extract the hobby as refer sheet 2 tab name "data1".
a) When click "search" button, the result will pull in column C tab "find"

2. Sheet 2 & 3 will show vlookup and the result show in sheet 4 the different when click button vlookup data 1 and data 2

Thank you very much

[lucas]

Apple has started a new thread here in regard to the question in post #22 above:
http://vbaexpress.com/forum/showthread.php?t=15797

Apple, if you are going to ask your question in several places it would be polite if you left a link so that folks aren't working on it in several places. This can be very annoying and can cause folks to not respond to any of your posts...be polite and make it as easy as possible for us to help you....

[lucas]

Nedy, If you are happy with the solution you received please mark your thread solved using the thread tools at the top of the page...