**Question description:**

*This user has given permission to use the problem statement for this blog.*

Vlookup or match help, having issues creating the formula

Solved by X. Y. in 18 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
14/11/2017 - 06:29

Hi, Welcome to Got It Pro.

Excelchat Expert
14/11/2017 - 06:29

I am observing the sample in the preview

User
14/11/2017 - 06:30

OKay

User
14/11/2017 - 06:30

So here we go

User
14/11/2017 - 06:30

I want a formula

User
14/11/2017 - 06:30

to search Column E and Column C to find who matches

User
14/11/2017 - 06:30

sorry

User
14/11/2017 - 06:30

they are not in orders

User
14/11/2017 - 06:30

and they wont be in order

User
14/11/2017 - 06:31

so check column E

User
14/11/2017 - 06:31

check column C

User
14/11/2017 - 06:31

find their match

Excelchat Expert
14/11/2017 - 06:31

As per the given data, 222 is matching in both C and E columns

User
14/11/2017 - 06:31

and then compare their answer in column A and in column D

User
14/11/2017 - 06:31

and tel me who matches

User
14/11/2017 - 06:32

So from columns A through E

User
14/11/2017 - 06:32

I want to get columns F and G

User
14/11/2017 - 06:32

questions?

Excelchat Expert
14/11/2017 - 06:32

Can you please explanation of G2 cell

User
14/11/2017 - 06:33

Look at F column

User
14/11/2017 - 06:33

999

User
14/11/2017 - 06:33

look at E and C

User
14/11/2017 - 06:33

thats 999

Excelchat Expert
14/11/2017 - 06:33

*Please provide the explanation for G2 cell.

User
14/11/2017 - 06:33

im gtting there sir

Excelchat Expert
14/11/2017 - 06:33

Sure

User
14/11/2017 - 06:33

999 of C4 gives 0 of P_R_0

Excelchat Expert
14/11/2017 - 06:33

C and E having 999. But, they are in different rows

User
14/11/2017 - 06:34

999 in E2

User
14/11/2017 - 06:34

gives 0 in P_R_1

User
14/11/2017 - 06:34

0 = 0

User
14/11/2017 - 06:34

so G2 = 1

Excelchat Expert
14/11/2017 - 06:34

Ok. I understood the requirement. I am working on it.

User
14/11/2017 - 06:34

IF they did not match (0=1)

User
14/11/2017 - 06:35

so G2 = 0

Excelchat Expert
14/11/2017 - 06:35

Thanks for the explanation.

Excelchat Expert
14/11/2017 - 06:36

=if(index(D:D, match(F2,E:E,0),1)=index(A:A, match(F2,C:C,0),1),1,0)

Excelchat Expert
14/11/2017 - 06:37

I am explaining the formula

Excelchat Expert
14/11/2017 - 06:37

I am first matching the rows using match function and then getting the corresponding 1,0 using index function.

Excelchat Expert
14/11/2017 - 06:38

When, the 2 values are matching, the result is 1. Otherwise, it is 0

Excelchat Expert
14/11/2017 - 06:40

So, the formula works well as per the problem description.

User
14/11/2017 - 06:41

im getting an error

User
14/11/2017 - 06:41

so this is dummie data

User
14/11/2017 - 06:41

I need it for a confidential document

User
14/11/2017 - 06:41

and when I copy pasted the formular

User
14/11/2017 - 06:41

its not running

Excelchat Expert
14/11/2017 - 06:42

Whether the column location are correct. I have written formula in column H. I can shift the formula to column G

Excelchat Expert
14/11/2017 - 06:42

Now, Please try copying the formula.

User
14/11/2017 - 06:42

So to the right is a better representatin

User
14/11/2017 - 06:43

Do I need column F?

User
14/11/2017 - 06:43

Or does ur formula G work without column F

User
14/11/2017 - 06:43

and does ur formula work with row 1 being inserted

Excelchat Expert
14/11/2017 - 06:43

If you does not need that, i can change the formula

Excelchat Expert
14/11/2017 - 06:44

Yes. It works with one row insertion

User
14/11/2017 - 06:44

I do need F

User
14/11/2017 - 06:44

Because F will tell me what G represents

User
14/11/2017 - 06:44

So G3 answer is specific for F3

Excelchat Expert
14/11/2017 - 06:45

Ok. So, in that case, the formula should work.

User
14/11/2017 - 06:45

it worked

Excelchat Expert
14/11/2017 - 06:45

Yes

User
14/11/2017 - 06:45

so 1 = match

User
14/11/2017 - 06:45

0 = no match

User
14/11/2017 - 06:45

correcT?

Excelchat Expert
14/11/2017 - 06:45

Yes. 1=match

Excelchat Expert
14/11/2017 - 06:46

and 0 is not match.

Excelchat Expert
14/11/2017 - 06:46

It works for any number of rows. As i am fixing the columns

User
14/11/2017 - 06:46

Its perfect

User
14/11/2017 - 06:46

you are amazing

User
14/11/2017 - 06:46

thank you for your work

Excelchat Expert
14/11/2017 - 06:46

Thank you.

User
14/11/2017 - 06:46

Have a nice day

Excelchat Expert
14/11/2017 - 06:47

You are welcome:) Please visit Got It Pro

Excelchat Expert
14/11/2017 - 06:47

Have a great day ahead

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*