Urgent help. Need vlookup forumla

[formula1234]

I have 2 files. FileA contains the item names. FileB contains a list of keywords. I need a formula that I can use on FileA on the second column that will scan all the keywords listed in FileB and put "1" if it matches and 0 if it doesn't match. I really need help on this. I need it for work and its due tomorrow. Thank you everyone!​

 

[AhoyNC]

Maybe something like this.

Excel Workbook
	A	B	C	D	E
1	File A	Key Word	Match		FileB
2		K1	0		K2
3		K2	1		K3
4		K3	1		K4
5		K4	1		K5
6		K1	0		K6
7		K6	1		K24
8		K7	0		K8
9		K24	1		K9
10		K9	1		K10
Sheet

 

[jtakw]

Hi,

Cross posted here: https://www.excelforum.com/excel-fo...1239746-urgent-help-need-vlookup-forumla.html

 

[formula1234]

hi

Can you make the formula to match partial as well? lets say there is a keyword that is "K1 cool" and the file B has only "k1" I need it to be "1". Thanks!

 

[jtakw]

Hi,

It's not as strict here, but you really should consider reading the forum rules if you want help without hipcups.

How about this:

Book1
	A	B
1	red hat	1
2	black hat	1
3	purple hat	0
4	pink hat	1
Sheet17

Cell Formulas
Range	Formula
B1	=--OR(ISNUMBER(LOOKUP(2,1/SEARCH(A1,Sheet18!A$1:A$5))),ISNUMBER(LOOKUP(2,1/SEARCH(Sheet18!A$1:A$5,A1))))

Book1
	A
1	red hat is cool
2	black hat
3	blue hat
4	cool red hat
5	pink
Sheet18

Change/adjust cell references/range as needed, change sheet names to match yours.

 

[formula1234]

thanks i really appreciate your help. But is there some sort of search limit that you put on the formula? because if I move the keyword "red hat is cool" down to row 100, it won't work.

 

[Aladin Akyurek]

In B2 of FileA enter and copy down:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&[FileB.xlsx]Sheet1!A$2:A$5&" "," "&A2&" ")))+0

where A2:A5 of Sheet1 in FileB houses the keywords of interest.

 

[jtakw]

As I said at the bottom of my post #5 , you Need to adjust the cell references/ranges to match your data set.

 

[formula1234]

sorry i'm a total newb. Can you give the formula if I need it to search 1,000,000 rows?

 

[Aladin Akyurek]

sorry i'm a total newb. Can you give the formula if I need it to search 1,000,000 rows?

See post #7 ...