How to find data in a cell??

[INEEDEXCELHELP]

Okay here goes!!

Basically I need a formula that work as below. It's over two sheets and is probably very easy to most of you I just cant get my head round this one!!

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.

Thanks in advance!

 

[Chris Macro]

If I understood your question correctly, I believe this should work for you:

=INDEX($B$1:$B$3000,MATCH(Sheet1!I1,$A$1:$A$3000,0))

 

[Smitty]

Welcome to the Board!

Have you tried VLOOKUP:

=IF(B1="TEXT1",VLOOKUP(I1,Sheet2!A1:B300,2,FALSE),"")

HTH,

 

[INEEDEXCELHELP]

=IF(B20="LL MATCH",VLOOKUP(I20,LL!$A$2:$A$3000,2,FALSE),"")

This is my version of your formula in my workbook, Smitty but I just get #N/A?

Just to try and make better sense of it, If b20 (im starting from 20 on this sheet!) on Sheet 'FEES-BATCHED' states "LL MATCH" I need it to look at L20 on 'FEES-BATCHED' (NOT I20 anymore!) which might state L21433 and see if that same value is in a cell on another sheet. ('LL') between a1 - a3000 if it finds that value in say cell:a2345 then it show the data found in b2345. Sorry to be a pain! And thank you for your help so far!!

 

[Smitty]

If you want to look up L20, then you need to change I20 in the formula to L20.

=IF(B20="LL MATCH",VLOOKUP(L20,LL!$A$2:$A$3000,2,FALSE),"")

 

[INEEDEXCELHELP]

If you want to look up L20, then you need to change I20 in the formula to L20.

=IF(B20="LL MATCH",VLOOKUP(L20,LL!$A$2:$A$3000,2,FALSE),"")

OOOOPS!! my bad! :P

however now it says #REF!

Im soo confused!!

 

[Smitty]

I should have caught that:

=IF(B20="LL MATCH",VLOOKUP(L20,LL!$A$2:$B$3000,2,FALSE),"")

You were referring A:A, but trying to reference the second column (2), which is outside of the reference range, hence the REF error. If you extend it to A:B you should be fine.

 

[INEEDEXCELHELP]

I should have caught that:

=IF(B20="LL MATCH",VLOOKUP(L20,LL!$A$2:$B$3000,2,FALSE),"")

You were referring A:A, but trying to reference the second column (2), which is outside of the reference range, hence the REF error. If you extend it to A:B you should be fine.

WOW thank you soooo much!!! your a genius!!

Now to add a exra bit :P if the content of 'LL' B2345 is empty the cell shows the content of e20 on 'FEES-BATCHED'

Is this possible?

 

[Smitty]

You'd probably want a nested IF statement. Something like this:

=IF(B20="LL MATCH",IF(VLOOKUP(L20,LL!$A$2:$B$3000,2,FALSE),'FEES-BATCHED'!E20,VLOOKUP(L20,LL!$A$2:$B$3000,2,FALSE)),"")

 

[INEEDEXCELHELP]

You'd probably want a nested IF statement. Something like this:

=IF(B20="LL MATCH",IF(VLOOKUP(L20,LL!$A$2:$B$3000,2,FALSE),'FEES-BATCHED'!E20,VLOOKUP(L20,LL!$A$2:$B$3000,2,FALSE)),"")

Sorry Smitty this comes up with #N/A?? what does that mean?

Also how did you come up with that formula? As I would like to know how to write this for myself in the future

Although I bow down to your knowledge!