What function to use to match a column against other and if matches value of another colum

[kps01]

What function to use to match a column against other and if matches value of another column to be used. In simple words:

File 1 Sheet 1 have a column A; Column B
File 2 Sheet 1 have a column A; Column C (Empty where formula goes)

I want to match values b/w these columns i.e. File 2:Col A = File1:Col A; if value matches, then Value from File1:Column B of the same row (where value matches) needs to go in File 2:Col C.

Hope I was able to explain. Let me know if any clarification is required.

Thanks in Adv!
Kp

[benishiryo]

Hi Kp, welcome to the forum. you cannot copy what i wrote since the worksheet references will be taken in. so you have to understand the VLOOKUP formula & apply it. let's say the range in File 1 Col A is A2:B100. try this in file 2 Col C:
=VLOOKUP(A2,

the above means you are looking at the value of A2 in File 2. now go to File 1 & select A2:B100. it should look something like this, but with different file & sheet name:
=VLOOKUP(A2,[Book1]Sheet1!$A$2:$B$100

continue with saying you want to return the 2nd column & find an exact match (i.e. the number 0):
=VLOOKUP(A2,[Book1]Sheet1!$A$2:$B$13,2,0)

to make sure that those cannot be matched don't show NA, place an IF & ISNA formula:
=IF(ISNA(vlookup formula),"",vlookup formula))

so it might look something like:
=IF(ISNA(VLOOKUP(A2,[Book1]Sheet1!$A$2:$B$13,2,0)),"",VLOOKUP(A2,[Book1]Sheet1!$A$2:$B$13,2,0))

[kps01]

Hi Benishiryo,

Thanks for your help. I am not able to apply this formula. To simply here is exactly what I want, would appreciate if you can reply an solution or I might have to try your earlier one if that is correct:

File 1
Col A Col B
10 Hello
12 Welcome

File 2
Col A Col x
12 Welcome
10

So Value from Col A i.e. 12 matched with Col A in File 1, and I need value from the col b where value matched i.e. Welcome in this case.

Thanks a lot once again
Kp

[FDibbins]

you will obviously have to change the file names to match you're real files, but use this in file 2 copied down...
=VLOOKUP(A3,'[File 1.xlsx]Sheet1'!$A$3:$B$4,2,FALSE)
It assumes that you're data is in columns A and B in file 1 and column A in file 2

[kps01]

Thanks FDibbins,

Yes ofcourse! I am referring to correct file name here. But what I am not able to understand in formulas above, we match value from col a(file1) to col a(file1) and if value matches then value from col b (file 1) gets populated in colx (file 2). Not sure if I am able to explain. Sorry.

For e.g. if I have to pick the value from same Col A (file 1) if matches, then here is what it would be and it works:
=VLOOKUP(A2,[Application_Master_List_20121107.xlsx]MASTER!$B:$B,1,0)
But I need value from col BA 2 (same row of A 2 obv).

Thanks everyone, you guys are proactive in replies.

[FDibbins]

vlookup() works as follows...

=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match

try changing you're formula to this...

=VLOOKUP(A2,[Application_Master_List_20121107.xlsx]MASTER!$A:$B,1,0)

it takes a value from file1 (A2), then looks down column A in file2 until it finds what you are looking for. when it finds what you are looking for, it takes the value of B from that row and puts it in file1

see the 2 attached files. hope they explain it better?

[kps01]

Gotch!... That worked!

Thanks a lot.

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)