if and match or find - any code or formula

[Vishaal]

HI All,

Thanks in advance,

we have the following sheets

Table 1

s.no	head 2	head 3
1	5246359
2	526589
3	659854
4	6325418
5	9584764

Table 2

s.no	head 5	t1	t2	t3
1	52463595	25.01.2020	text 1	Rozi D mario
2	9584764	26.01.2020	text 2	Dino Mario
3	659854569	27.01.2020	text 3	Suchil
4	526589	28.02.2020	text 4	Obam
5	6325418	30.02.2020	text 5	Tony

we are trying to do following:-

in sheet1!C2, if sheet1!B2 isequalto sheet2!B2:C and when we find match get next column value
i mean you can see sheet1!B2 value match to sheet2!B5 then in sheet1!C3, we get the value sheet2:C5

 

[Fluff]

How about
=VLOOKUP(B2,Sheet2!$B$2:$C$100,2,0)

 

[Vishaal]

Thanks Fluffji

Bit can we do ot without vlookup,

 

[Fluff]

Why don't you want to use vlookup?

 

[Vishaal]

thanks for your help Fluffji, but i want other option rather than vlookup

using vlookup, we can do this, i know but i required other option

thanks for your help, you solved my query, i know there are many other queries which required your time and effort, but if you can provide other option, it will help him

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this. Thanks

 

[Vishaal]

excel 2010

 

[Fluff]

Thanks for that, please remember to update your account details. It saves members having to ask all the time.

Try
=INDEX(Sheet2!$C$2:$C$100,MATCH(B2,Sheet2!$B$2:$B$100,0))

 

[Vishaal]

sure i will do that, and thanks for the solution

 

[Fluff]

You're welcome & thanks for the feedback.