In Excel, How to get cell reference of a particular word in anoth.

[Nivas]

Hi, Actually I have to copy a value from one sheet to another which I had
done easily by typing '=' and then clicking on the cell in that sheet.

But, I want to search for a word in another sheet, find the cell reference
which is next to that word.

For eg.,
In c5 value is 'YZ Bank'& in C6 value is 10
In D5 value is 'BC Bank'& in D6 Value is 16

What actually I need is,
In a new sheet, I want to search for 'BC Bank' and get the value 16 from
that sheet.

Please help me with this.

Thanking you
Best regards.

 

[Bob Phillips]

Nivas,

Here is a starter

Dim oCell As Range

Set oCell = Cells.Find(What:="BC Bank", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not oCell Is Nothing Then
MsgBox oCell.Offset.Address & " contains " & oCell.Offset(0,
1).Value
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

Have a look in HELP index for Vlookup

 

[Sharpy]

Hi Nivas:
Command to use is...
=HLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Example =HLOOKUP(D1,A2:B20,2,TRUE) placed in D2 cell
where...
D1 = The information to lookup (search) cell data, enter for example 'BC
Bank' in cell A1. (Use any cell or even 'sheet2' cell.)
'A2:B20' = The Range of your data to lookup. 2xFields and 19xRecords.
The 1st Column is your data record fields starting from
left to right,
The 2nd Column is the 1st data record,
The 3rd Column is the 2nd data record,
The 4th Column is the 3rd data record etc.,
'2' = The database row number you wish to lookup data. 1st cell of the range
on the top LHS is row 1.
'TRUE' = Whether you wish the action to be True or False (should be TRUE in
most cases).

TIPS
1: The 1st row of your data records must be the search data entered in cell
D1 of the formula.
2: Define the database range by giving it a name. "Insert/Name/Define" from
the menu.
3: Click on the 'fx' command button (very small) just to the left of the
formula entry, for ease of entry & help.
4: If your data fields are horizontal (Records would also be horizont) then
use the VLOOKUP command instead.
5: If using another excel sheet for the data the syntax is...
=VLOOKUP(D1,Sheet2!A2:B20,2,TRUE).
6: If using another excel filename for the data the syntax is...
=VLOOKUP(D1,[filename.xls.Sheet2]Sheet2!A2:B20,2,TRUE).
where...
'filename' = the other excel filename,
'Sheet1' = the sheet name used in that filename,
'!' = signifies the end of the lookup data source formula string (I
guess).
8: Value of col_index_num: If the data you wish to lookup changes rows, then
you must find the location of the 'BC Bank' cell first and add 1 to it if
it's to the right of that cell.
9: During the lookup process you may have incorrect results if the search
data is not unique.
10: Another formala you may want to try is
MATCH(lookup_value,lookup_array,match_type)

eg. Assuming you have only 2 fields and 20 records and they are on sheet2 &
D1 is the Current Sheet1 =hlookup(D1,Sheet2!A1:B20,2,TRUE)

eg. for below.
All on the same Sheet =HLOOKUP(A1,C5:H6,2,TRUE) in cell B1.
A B C D
1 BC Bank =HLOOKUP(A1,C5H6,2,TRUE)
2
3
4
5 YZ Bank DC Bank
6 10 16

Wow, looking back, that looks more complicated than I ever thought.
I've had a quick check and hope it's accurate and I covered the important
aspects.

Cheers Sharpy