Searching for values in a range, matching to a cell - FAIL

[teston]

Hello,

I have two sheets:

Sheet 1 contains data I need to process:

Book1
	A	B	C
1		Size	Fruit
2	big apples
3	small oranges
4	medium pears
5	huge watermelons
6	big apple
7	small orange
8	medium pear
9	huge watermelon
Sheet1

Sheet 2 is a database of values:

Book1
	A	B
1	Size	Fruit
2	big	apple
3	small	orange
4	medium	pear
5	huge	watermelon
Sheet2

I need to populate empty cells in Sheet 1 with matching values from Sheet 2 so that final result looks like this:

Book1
	A	B	C
1		Size	Fruit
2	big apples	big	apple
3	small oranges	small	orange
4	medium pears	medium	pear
5	huge watermelons	huge	watermelon
6	big apple	big	apple
7	small orange	small	orange
8	medium pear	medium	pear
9	huge watermelon	huge	watermelon
Sheet1

I have tried this formula:

=IF(ISNUMBER(SEARCH(Sheet2!A:A, $A2)),Sheet2!A:A,"")

but it only works for cells with plural values, not singular:

Book1
	A	B	C
1		Size	Fruit
2	big apples	big	apple
3	small oranges	small	orange
4	medium pears	medium	pear
5	huge watermelons	huge	watermelon
6	big apple	0	0
7	small orange	0	0
8	medium pear	0	0
9	huge watermelon	0	0
Sheet1

Cell Formulas
Range	Formula
B2:C9	B2	=IF(ISNUMBER(SEARCH(Sheet2!A:A, $A2)),Sheet2!A:A,"")

What would be the best way to achieve this? (Please note that it's important to use the partial match - apple should match apple & apples).

Thanks!

 

[jtakw]

Hi,

If your Sheet1 Size is Always on Sheet2, you can simply use E2 formula for Size, otherwise, if you have values like A11, then use B2 formula copied across for Both Size and Fruit:

Note: I highly recommend Not using Entire Column references (i.e. A:A, B:B, etc.), you'll definitely notice a performance degradation, just use a range you know is more than sufficient (i.e. A2:A20000).

Book1
	A	B	C	D	E
1		Size	Fruit		Size
2	big apples	big	apple		big
3	small oranges	small	orange		small
4	medium pears	medium	pear		medium
5	huge watermelons	huge	watermelon		huge
6	big apple	big	apple		big
7	small orange	small	orange		small
8	medium pear	medium	pear		medium
9	huge watermelon	huge	watermelon		huge
10	medium pineapple	medium			medium
11	tiny apples		apple
Sheet1

Cell Formulas
Range	Formula
B2:C11	B2	=IFERROR(LOOKUP(2,1/(SEARCH(" "&Sheet2!A$2:A$200," "&$A2)*(Sheet2!A$2:A$200<>"")),Sheet2!A$2:A$200),"")
E2:E10	E2	=LEFT(A2,FIND(" ",A2)-1)

Book1
	A	B
1	Size	Fruit
2	big	apple
3	small	orange
4	medium	pear
5	huge	watermelon
Sheet2

 

[BSALV]

Map1
	A	B	C
1		Size	Fruit
2	big apples	big	apple
3	small oranges	small	orange
4	medium pears	medium	pear
5	huge watermelons	huge	watermelon
6	big apple	big	apple
7	small orange	small	orange
8	medium pear	medium	pear
9	huge watermelon	huge	watermelon
sheet1

Cell Formulas
Range	Formula
B2:C9	B2	=IFERROR(INDEX(sheet2!A$1:A$5,1/(1/MAX(ISNUMBER(SEARCH(sheet2!A$1:A$5,sheet1!$A2))*ROW(sheet2!A$1:A$5)))),"?")

becomes quick an time-consumming formula

 

[jtakw]

@BSALV , just a note, your formula will pick up "apple" for "pineapple" in my sample A10, and error out if there are Blanks in Sheet2 range.

 

[teston]

Thanks jtakw! You have saved my life

Is it possible to make it pick the first matching value from Sheet2 (assuming it looks up top to bottom)?

Currently it works like this:

Book1
	A	B	C	D
1		Size	Fruit	Definition
2	11 huge and very big apples	huge	apple	very
3	their big and very huge apple	huge	apple	very
Sheet1

Cell Formulas
Range	Formula
B2:D3	B2	=IFERROR(LOOKUP(2,1/(SEARCH(" "&Sheet2!A$2:A$200," "&$A2)*(Sheet2!A$2:A$200<>"")),Sheet2!A$2:A$200),"")

with Sheet2 values being in this sequence:

Book1
	A	B
1	Size	Fruit
2	big	apple
3	small	orange
4	medium	pear
5	huge	watermelon
6		berry
Sheet2

So that final result looks like this:

Book1
	A	B
1		Size
2	11 huge and very big apples	big
3	their big and very huge apple	big
Sheet1

Thank you so much!

 

[jtakw]

Well, there's a problem here, you have Both "huge" and "big" in the same cell, what's the logic, and how will Excel know which is the correct outcome?

So, if the logic is basically an Exact match (less the singular/plural part), then we concatenate the 2 columns in Sheet2 with a wild card, like this:

Book1.xlsx
	A	B	C
1		Size	Fruit
2	big apples	big	apple
3	small oranges	small	orange
4	medium pears	medium	pear
5	huge watermelons	huge	watermelon
6	big apple	big	apple
7	small orange	small	orange
8	medium pear	medium	pear
9	huge watermelon	huge	watermelon
10	medium pineapple
11	tiny apples
12	11 huge and very big apples	big	apple
13	their big and very huge apple	big	apple
Sheet1

Cell Formulas
Range	Formula
B2:C13	B2	=IFERROR(LOOKUP(2,1/(SEARCH(" "&Sheet2!$A$2:$A$200&"*"&Sheet2!$B$2:$B$200," "&$A2)*(Sheet2!A$2:A$200<>"")),Sheet2!A$2:A$200),"")

Book1.xlsx
	A	B
1	Size	Fruit
2	big	apple
3	small	orange
4	medium	pear
5	huge	watermelon
Sheet2

 

[BSALV]

Is it possible to make it pick the first matching value from Sheet2 (assuming it looks up top to bottom)?

big is before huge in sheet2, so it should show big

 

[BSALV]

Teston.xlsx
with 2 named ranges

Again my warning, this are very intense formulas.
they 'll slow down the performance of your worksheet if there are too much of them or the size of the table in sheet2 is big.
It's perhaps better to let them do their job and afterwards replace them with their value !

Map1
	D	E	F	G	H	I	J
4
5
6		Size	Fruit	definition
7		big	apple	x
8		small	orange	y
9		medium	pear	z
10		huge	watermelon	a
11
12
13		Offset_Tbl	=RIJ(TBL_Database[[#Kopteksten];[Size]])
14		TBL_C1	=TBL_Database[Size]
15
16
sheet2

Map1
	A	B	C
1		Size	Fruit
2	11 huge and very big apples	big	apple
3	their big and very huge apple	big	apple
4	big apples	big	apple
5	small oranges	small	orange
6	medium pears	medium	pear
7	huge watermelons	huge	watermelon
8	big apple	big	apple
9	small orange	small	orange
10	medium pear	medium	pear
11	huge watermelon	huge	watermelon
12	a_big_apple, pineapple, britney spears, orange	?	orange
13	big apples	big	apple
14	small oranges	small	orange
15	medium pears	medium	pear
16	huge watermelons	huge	watermelon
17	big apple	big	apple
18	small orange	small	orange
19	medium pear	medium	pear
20	huge watermelon	huge	watermelon
Blad1

Cell Formulas
Range	Formula
B2:C20	B2	=IFERROR(INDEX(OFFSET(TBL_C1,,COLUMN()-2,,),MIN(IF(ISNUMBER(SEARCH(" " &OFFSET(TBL_C1,,COLUMN()-2,,)," " &$A2)),ROW(TBL_C1),9E+99))-Offset_Tbl),"?")