Search a spreadsheet for multiple values

Question

Hello,

I'm not sure if I'm searching the right thing, but I'm needing some help which I'm hoping you can assist with.

I have 2 spreadsheets. One containing banking information with money in/out and invoice numbers. The second spreadsheet is a records sheet where my staff members input data, which should include the invoice numbers and their respective values. This is so that I can check the figures inputted into their spreadsheet is the same as our incoming/outgoing of our bank. I hope this makes sense?

An example:

Workbook 1

INVOICE NUMBER	CURRENCY	IN/OUT
856399	GBP	-97.50
856400	GBP	-280.00
856401	GBP	24.00
856402	GBP	-97.50
856404	GBP	85.00

Workbook 2

INVOICE NUMBER	AMOUNT	PAID DATE
856399	£        97.50	30-Sep-15
856349	£        85.00	21-Sep-15
856570	£        85.00	30-Sep-15
856401	£        24.00	15-Sep-15
856404	£        97.50	14-Sep-15
856380	£       170.00
856400	£        280.00

What I need to do:

I would like to be able to search workbook 2 for ALL of the invoice numbers in workbook 1. Effectively, create a formula that will do a search from column A of workbook 1 to see if they're in workbook 2. However, there are multiple invoice numbers on workbook 2 so I would like to be able to search the entire sheet. I was hoping this could be done with a formula so that if the invoice number from workbook 1 is found in workbook 2 it returns a "found" or "not found" string.

I'm currently doing this manually (copy the invoice number from workbook 1, clicking in workbook 2 and searching with ctrl+f).

There must be an easier and faster way to do this? I've got 100's to go through and it can take up a large amount of time to do so.

All your help is massively appreciated.

Thanks,

Daniel

Answer 1

Use following formula in Workbook 1 (Replace [Book3]Sheet1! by your second workbook name) and drag down -

=IF(ISNUMBER(MATCH(A2,[Book3]Sheet1!$A:$A,0)),"Found","Not Found")

Note - Both Workbooks should remain open.

Answer 2

Thank you so much! You don't realise how much time you've saved me!!

Regards,

Daniel