Search two columns using one criteria return third column

[doug2019]

The results in column "N" are either in column "T" or "U" I need to be able to find the matching number in Column "T" or "U" and return what is in column "V" into column "K". If no results are found it needs to say "check lookup table"

Index, Match will only look at one column, not sure what else will help me

Thanks Doug

A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​	R​	S​	T​	U​	V​
1	100614, Standard, Pin Stakes - 12"	Transfer	Charge	184.000	0.00		$0.70	$139.84		3610.02 Small Tool Equipment			100614	Code in Cell N1​ =LEFT(B11,SEARCH(",",B11)-1)					100614	565	3610.02 Small Tool Equipment
2	3373, VC 150-10 X, Shop Vac, HEPA, w/ Outlet	Transfer	Charge	184.000	1.92	$353.28	$353.28						3373						121513	3373	3610.02 Small Tool Equipment
3	3396, DCH273, Rotohammer, Cordless, Small	Transfer	Charge	184.000	0.78	$135.18	$135.18						3396						420079924	89	3610.03 Large Tool Equipment

 

[Fluff]

Hi & welcome to MrExcel
How about
=IFERROR(INDEX($V$2:$V$4,IFERROR(MATCH(N2,$T$2:$T$4,0),MATCH(N2,$U$2:$U$4,0))),"check lookup table")

 

[doug2019]

Hi & welcome to MrExcel
How about
=IFERROR(INDEX($V$2:$V$4,IFERROR(MATCH(N2,$T$2:$T$4,0),MATCH(N2,$U$2:$U$4,0))),"check lookup table")

Yes this works, Thank You I was pulling out my hair and I don't even have any

 

[Fluff]

You're welcome & thanks for the feedback

 

[doug2019]

Yes this works, Thank You I was pulling out my hair and I don't even have any

Is there anyway to add a wildcard in case there is alpha charters within the numerical, or all alpha charters, this formula with only work on numerical, I have some instances where this will show up 10110A, or Delivery Charge will be part of the lookup.

 

[Fluff]

Can you post some examples of the data?

 

[doug2019]

The results in column "N" are either in column "T" or "U" I need to be able to find the matching number in Column "T" or "U" and return what is in column "V" into column "K". If no results are found it needs to say "check lookup table" This Formula works only on numerical =IFERROR(INDEX($V$2:$V$4,IFERROR(MATCH(N2,$T$2:$T$4,0),MATCH(N2,$U$2:$U$4,0))),"check lookup table") I have added Value in front of N2 VALUE(N2) but it will not recognize alpha characters

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

100614, Standard, Pin Stakes - 12"

Transfer

Charge

184.000

0.00

$0.70

$139.84

3610.02 Small Tool Equipment

100614

Code in Cell N1 =LEFT(B11,SEARCH(",",B11)-1)

100614

565

3610.02 Small Tool Equipment

2

3373A, VC 150-10 X, Shop Vac, HEPA, w/ Outlet

Transfer

Charge

184.000

1.92

$353.28

$353.28

3373A

121513

3373A

3610.02 Small Tool Equipment

3

Delivery Charge, DCH273, Rotohammer, Cordless, Small

Transfer

Charge

184.000

0.78

$135.18

$135.18

Delivery Charge

Delivery Charge

89

3610.03 Delivery Charge

 

[Fluff]

That formula works for me regardless of whether the values are text or numerical.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	1	100614, Standard, Pin Stakes - 12"	Transfer	Charge	184	0		$0.70	$139.84		3610.02 Small Tool Equipment			100614						Delivery Charge	89	3610.03 Delivery Charge
3	2	3373A, VC 150-10 X, Shop Vac, HEPA, w/ Outlet	Transfer	Charge	184	1.92	$353.28	$353.28			3610.02 Small Tool Equipment			3373A						100614	565	3610.02 Small Tool Equipment
4	3	Delivery Charge, DCH273, Rotohammer, Cordless, Small	Transfer	Charge	184	0.78	$135.18	$135.18			3610.03 Delivery Charge			Delivery Charge						121513	3373A	3610.02 Small Tool Equipment
5											test2			abc							123a	test1
6											test1			123a							abc	test2
7											check lookup table			a123						123		test3
Report

Cell Formulas
Range	Formula
K2:K7	K2	=IFERROR(INDEX($V$2:$V$7,IFERROR(MATCH(N2,$T$2:$T$7,0),MATCH(N2,$U$2:$U$7,0))),"check lookup table")

 

[doug2019]

After removing the VALUE and using your exact code it works for me to, thanks again for all your help

 

[Fluff]

You're welcome & thanks for the feedback