Match Text in Excel

Copy this formula to sheet 2 B1 and drag down

=IF((Sheet1!A1=Sheet2!A1),Sheet1!B1,"")

... more precisely::

A1 to A20 on Sheet2 :: Hi-Ho
B1 on Sheet2 :: Copy This

A1 on Sheet1 :: Hi-Ho
B1 on Sheet1 :: =IF(and(Sheet1!A1=Sheet2!A1,Sheet1!A1=Sheet2!A2,Sheet1!A1=Sheet2!A3,Sheet1!A1=Sheet2!A4,Sheet1!A1=Sheet2!A5,Sheet1!A1=Sheet2!A6,Sheet1!A1=Sheet2!A7,Sheet1!A1=Sheet2!A8,Sheet1!A1=Sheet2!A9,Sheet1!A1=Sheet2!A10,Sheet1!A1=Sheet2!A11,Sheet1!A1=Sheet2!A12,Sheet1!A1=Sheet2!A13,Sheet1!A1=Sheet2!A14,Sheet1!A1=Sheet2!A15,Sheet1!A1=Sheet2!A16,Sheet1!A1=Sheet2!A17,Sheet1!A1=Sheet2!A18,Sheet1!A1=Sheet2!A19,Sheet1!A1=Sheet2!A20),Sheet2!B1,"")

Notepad's very useful in editing huge formulas

Alternate is to write custom function in Module use FOR loop to scan Sheet2 -- i donno whitch is less CPU exhausting

I'm assuming that you mean..copy sheet2 B1 to sheet1 B1 as you would create a loop trying to copy sheet1 B1 to itself.

I would suggest that if these are numbers in cells A1:A20 that you total them in an adjacent cell and then use that cell for the transfer.

For example, if you total them in sheet2 cell A21 then you could insert the following formula in Sheet 1 B1..

=IF(Sheet1!A1=Sheet2!A21,Sheet2!B1,"No match")

Presumably you mean if Sheet1!A1 = any of the range A1:A20 in Sheet2,then copy.

This could, I imagine, be done with a nested if statement, but it would be complex and too deep for Excel to handle (limit is 7 deep if I recall correctly).

I'd do it with a macro, basically setting "Value" to Sheet1! A1 and searching all of the range A1:A20 at Sheet 2, a cell at a time, to see if there was an equal item, copying out if so, and moving to the next if not.

I am guessing you will now ask for the macro code, but if you check some available code out there and learn how to a, "For Value = 1 to 20, loop type routine", you'll be able to work it out - and be better equipped for next time.

There are likely to be more elegant ways that an Excel guru may know, but then I am not a guru.

I assume you mean that if the value in sheet1!A1 is to be found in the range sheet2!A1:20 then copy. If that is the case then you can use the VLOOKUP function:

=IF(sheet1!A1=VLOOKUP(sheet2!A1:A20,1,false),sheet1!B1,"")

Put that formula into the cell you are copying to. The value in bold is the cell you are copying from.

I assume you want to enter a value in cell A1 on sheet1 and, if that value shows up in Sheet2!A1:A20 put the value from the corresponding row in column B on Sheet2 into cell B1 on Sheet1.

- - - - - - - - - - - -

Big things to think about:

1) I assume you are looking for an exact match. You need to be sure to do that. The default versions of LOOKUP functions assume data in order and return the "closest" lower value unless you specifically tell them not to and do not work if the data you have is out of order. You can avoid this "challenge" by using MATCH and OFFSET functions

2) Is the value you enter guaranteed to be on sheet2? If so then you don't need to worry about handling an error. If not, then you need to handle the error.

- - - - - - - - - - - - -

If the data is guaranteed to be in $A$1:$A$20, then enter either of the following into cell Sheet1!B1

1. Using VLOOKUP: =VLOOKUP(A1,Sheet2!$A$1:$B$20,2,FALSE)

2. Using Match and Offset (The old school approach): =OFFSET(Sheet2!$B$1,MATCH(A1,Sheet2!$A$1:$A$20,0)-1,0)

On the other hand, if the data is NOT guaranteed to be in $A$1:$A$20, then enter either of the following into cell Sheet1!B1. (All you've done is add an error check on the result of finding the entry in the formulas from above... in bold)

1. Using VLOOKUP: =IF(ISERROR(VLOOKUP(A9,Sheet2!$A$1:$B$20,2,FALSE)),"Not Found",VLOOKUP(A9,Sheet2!$A$1:$B$20,2,FALSE))

2. Using Match and Offset: =IF(ISERROR(MATCH(A1,Sheet2!$A$1:$A$20,0)),"Not Found",OFFSET(Sheet2!$B$1,MATCH(A1,Sheet2!$A$1:$A$20,0)-1,0))

In both cases, you can drag the formula down to deal with more than one entry in column A of Sheet1.