find, match and fill cells in Excel spreadsheets Topic is solved

[carno]

I have Excel sheet 1 with 2 column A and B. Column A contains Japanese (kanji) terms and Column B contains their English translations. Excel sheet 2 also contains 2 columns A and B. Column A contains non-English terms and Column B contains no translations. I could copy Columns A and B from sheet 2 to sheet 1 and call them Columns C and D so all 4 columns are in the same sheet 1. My attempt is to compare Columns A in both spreadsheets (or Columns A and C in sheet 1 when all columns are in the same sheet) and fill all cells in Column B in sheet 2 (or Column D in sheet 1 when all columns are in the same sheet) and fill it automatically when any cells in Columns A in both sheets (or Columns A and C in sheet 1 when all columns are in the same sheet) match since I see a lot of matches (similar terms in both columns). I believe this is a "cross-check" operation.

[FanaticGuru]

If I am understanding correctly you want sheet 2 to look in sheet 1 for a word and if it finds it copy the translation from sheet 1 to sheet 2.

If that is correct, that can be done very easily within Excel using the LookUp command.

FG

[carno]

Yes, that is correct. Or I could copy sheet 2 columns into sheet 1 and have 4 columns A, B, C, D. Then look at column C and if it finds it in column A then copies from from column B (translation of term in column A) to column D (translation of column C).

Column A | Column B | Column C | Column D
term 1 | translation | term 2 | blank cell

if term 1 is identical to term 2 (that is, if term 2 is found in column A) then copy translation for terms 1 (in column B) to blank cell in column D (that would be translation for term 2). PS: Each column A, B, C and D has over 100 rows.

[FanaticGuru]

Whether they are in the same sheet or not does not matter. Does not really make it any easier or harder.

=IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE),"NOT FOUND")
If you put this in B1 of Sheet 2 and then copy and paste it into every cell you want below. B2, B3, etc.

Then any thing in Sheet 2 column A that is found in Sheet 1 column A will have the B column copied into where this formula is.

This is fairly basic lookup stuff that Excel is designed to do without any help from AHK or any outside program. After you get the Sheet 2 column B populated if you don't want it to update anymore you can copy and paste it as text to remove formula.

It could be done with AHK if really needed.

FG

[carno]

Thanks a lot Guru, I'll try and let you all know.

[carno]

I'm still on Excel 2003 so IFERROR does not work. However, I found this one working perfectly!

Code: Select all

=IF(ISERROR(VLOOKUP(C1,$A$1:$B$100,2,FALSE)),"Not Found",VLOOKUP(C1,$A$1:$B$100,2,FALSE))