Help with a formula please!!!

[INEEDEXCELHELP]

Okay here goes!! I am new to this so if its an easy question I am really sorry!!

I have to get this done for my boss as soon as possible!!

Basically this is what I need as a formula. Plus it's over two sheets.

If B1 (sheet 1) = "TEXT1" ,FIND the content I1 (sheet 1) within a1:a3000 (sheet 2). If I1 is found in that range i need to know the text in the cell next to the found text.

eg: the text of I1 (sheet 1) was found in cell a234 (sheet 2) so it gives me the text in b234.

I hope this make sense??

Any help will be greatly appreciated as I had to get this back to my boss yesterday!!

ARGH I JUST CAN'T WORK THIS ONE OUT!!!

Thanks again!!

 

[ArthriticPanda]

Hi,

Without knowing how new you are to excel I will try and see if I understand you correctly and offer up one way you could do this in formula...

The first thing I would do is create a named range for your data on sheet2, let's call it MyRange. If you are unfamiliar with how to do this there is a short tutorial at this link:

http://www.contextures.com/xlNames01.html#NameBox

but basically all you need to do is select the range from A1:B3000 and whilst it is selected you can type the name in the Name Box just above column A.

Once you have done that, go back to sheet1 and type an IF statement with a VLOOKUP in the cell J1.

=IF(B1="TEXT1",VLOOKUP(I1,myRange,2,0),"")

This formula is saying that if TEXT1 is in cell B1 then lookup the contents of cell I1 in myRange and return the result in from column 2 (in this case Sheet2, column B). If it doesn't = TEXT1 in cell B1 on sheet1 then return a blank.

I'm not sure if this is what you are looking for but I hope it will at least give you an idea.

NAMED RANGE on sheet2

Excel Workbook
	A	B
1
2
3
4
5
6
7
8	Some text	DOG
9
10
11
12
13
14
15
16
17
18
19
20	Some more text	CAT
21
22
23
24
Sheet2

FORMULA ON SHEET1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		TEXT1							Some text	DOG
2									Some more text
3
Sheet1

AP

 

[ArthriticPanda]

...one other thing to remember, I realise you are pressed for time with your request, but you will need to take time out to read the rules on posting otherwise one of the moderators will pull you up for it.