ExcelBanter - Match and update Excelsheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Match and update Excelsheets (https://www.excelbanter.com/excel-programming/383047-match-update-excelsheets.html)

Match and update Excelsheets

Hi,

I have two 2 excel sheet. Sheet1 contains the emp# and noofaccess.
Sheet2 contains the employee# and noofaccess ( more than 6 times).
Based on the information from sheet1, I need to update the noofaccess
(more than 6 times) column in sheet2. For example,

Sheet1:

emp# noofacess
123 6
234 3
345 7
456

Sheet2
emp# noofaccess(more than 6 times)
123 Yes
234 No
345 Yes
456 N/A
678 N/A

COuld you please advice how to do that?. I tried the following
formula.
=IF(COUNTIF(Sheet4!A1:A4,A5)0,IF(B16,"Yes")), but its not working
out.

Match and update Excelsheets

In B5 of Sheet2 as an example:

=IF(ISERROR(MATCH(A5,Sheet1!A:A,0)),"No",IF(INDEX( Sheet1!B:B,MATCH(A5,Sheet1!A:A,0),1)6,"Yes","No") )

drag fill up and down the column.

--
Regards,
Tom Ogilvy

"Vandy" wrote:

Hi,

I have two 2 excel sheet. Sheet1 contains the emp# and noofaccess.
Sheet2 contains the employee# and noofaccess ( more than 6 times).
Based on the information from sheet1, I need to update the noofaccess
(more than 6 times) column in sheet2. For example,

Sheet1:

emp# noofacess
123 6
234 3
345 7
456

Sheet2
emp# noofaccess(more than 6 times)
123 Yes
234 No
345 Yes
456 N/A
678 N/A

COuld you please advice how to do that?. I tried the following
formula.
=IF(COUNTIF(Sheet4!A1:A4,A5)0,IF(B16,"Yes")), but its not working
out.

Match and update Excelsheets

On Feb 12, 3:15 pm, Tom Ogilvy
wrote:
In B5 of Sheet2 as an example:

=IF(ISERROR(MATCH(A5,Sheet1!A:A,0)),"No",IF(INDEX( Sheet1!B:B,MATCH(A5,Sheet*1!A:A,0),1)6,"Yes","No" ))

drag fill up and down the column.

--
Regards,
Tom Ogilvy

"Vandy" wrote:
Hi,

I have two 2 excel sheet. Sheet1 contains the emp# and noofaccess.
Sheet2 contains the employee# and noofaccess ( more than 6 times).
Based on the information from sheet1, I need to update the noofaccess
(more than 6 times) column in sheet2. For example,

Sheet1:

emp# noofacess
123 6
234 3
345 7
456

Sheet2
emp# noofaccess(more than 6 times)
123 Yes
234 No
345 Yes
456 N/A
678 N/A

COuld you please advice how to do that?. I tried the following
formula.
=IF(COUNTIF(Sheet4!A1:A4,A5)0,IF(B16,"Yes")), but its not working
out.- Hide quoted text -

- Show quoted text -

Its working great. Thanks you very much for the quick response.

Thanks a lot..

-Chinna