Fairly simple question

[Gokrish]

I have 2 different spreadsheets with same 2670 rows of data.

Spreadsheet 1
Column A - serial #
ColumnB - Description
Column C - Y/N (Responses)

Spreadsheet2
Column A - Serial #
Column B - Description
ColumnC - I need to enter "1" 1f the responses is 'Y" from spreadsheet 1, else i need to enter 0 if the response is "N"from spreadsheet 1.

Since the volume is huge I am looking for an easier way to do this. Can someone please help me with this. I am sure there is an easy way around with Vlookup or Hlookup, but need some guidance. This is kind of urgent.

Thanks
Avanthi

 

[c_m]

Try in C2 of Sheet2:
=(index(Sheet1!$C$2:$C$100,match(A2&B2,Sheet1!$A$2:$A$100&Sheet1!$B$2:$B$100,0))="Y")+0
adjust ranges accordingly

 

[Blade Hunter]

=IF(TRIM(UPPER(VLOOKUP(A2,[Book8]Sheet1!$A:$C,3,0))) = "Y",1,0)

Replace Book8 with the name of the workbook you wish to lookup the result from and Sheet1 with the name of the sheet.

The formula will return #N/A if the serial does not exist in your lookup sheet

 

[c_m]

did you mean two different spreadsheets (i.e. worksheets) or workbooks (file)?

 

[Beate Schmitz]

Hello,

formula of cell C2 can be copied down:

Excel Workbook
	A	B	C
1	Serial #	description	Y/N
2	No.1	kind19	0
3	No.2	kind1	1
4	No.3	kind13	0
5	No.4	kind20	0
6	No.5	kind23	1
Sheet2