Formula for match value

[santosh226001]

hello

We have two sheet in my workbook 1) Prarup-2 & 2)roadlist

We want to compare Roadlist colum B Value with Prarup-2 sheet ... Colum C

if Value Match then in Roadlist Sheet Display "Enter" in Column I otherwise that show <blank>

Currently we use following Vlookup formula that's work fine..

IF(ISERROR(VLOOKUP(B10,'Prarup-2'!$C$8:$C$1500,1,FALSE)=B10),"","Enter")

but we want this formula with index and match function....

Thank you

[martindwilson]

funny way of using vlookup
and why do you want index match
just match will do =if?(isnumber(match(b10,Prarup-2'!$C$8:$C$1500,0),"Enter","")
or countif
=if(countif(Prarup-2'!$C$8:$C$1500,b10),"enter","")
but since you asked the index/match version would be
IF(ISERROR(index(Prarup-2'!$C$8:$C$1500,match(B10,Prarup-2'!$C$8:$C$1500,0)=b10),"","Enter")

[Fotis1991]

Why not using COUNTIF?

=IF(COUNTIF(Prarup-2'!$C$8:$C$1500,B10);"Enter","")

[Debraj Roy]

If INDEX & MATCH both functions are mandatory..
you can use this too..

=INDEX({"Enter",""},ISNA(MATCH(B2,'Prarup-2'!C:C,0))+1)

[santosh226001]

firstly i m not in expert in forumal using so as per my knowledge we use vlookup in funnyway ;-) no problem

all your exper advice work as per my requirement... we use index & match for this resion coz in HINDI font countif some speical character not understand so forumal return #NA

we add reputation to all of you.

Thank you very much.. Debraj, martindwilson & Fotis1991

[Debraj Roy]

Hi santosh,

you can share workbook, (if not so secure) so coz I never work on any hindi excel font.. wnat to learn how to match in this case..

in the meanwhile.. you can try this too..

=INDEX({"Enter",""},ISNA(MATCH("*"&B2&"*",'Prarup-2'!C:C,0))+1)

[santosh226001]

sir my file is more then 1 mb so how can i send u... actually that is my project file where we currently working & in final stage .. but we want to share that file with you..so that u can suggest me few more formulas...
my project is working is mS 2003
u send me email id so we send u that file with u on email with brieaf details

Santosh