Vlookup , Find, Left -> Excel Functions used together

[ELENA911]

Hi,

I am trying to combine the following functions in excel:

Vlookup
Find
Left

I am workign on a spreadsheet and there are few tables in there that I need to obtain information on the summary page.
My issue is vlookup only works if the value is exact or close enough. I need to put a condition if the criteria doesnt meet to find the lookup value within the cell and return the value in the cell adjacent to it. Can someoen please help me?


for Example in my summary table i have the following name:


Bank of America

i am looking it up in a different table where I have

Apple 10
Bank of New York 5
Bank of America, Inc 5

I need a function that will find bank of america and input 5 in the summary table.

is it possible?

thanks

 

[Robert Mika]

Will the number be always a last part of the of the string?
In case if your list is in column A of this example you can use:

=IF(ISNUMBER(MATCH("*Bank of America*",$A$1:$A$15,0)),RIGHT(INDEX(A1:$A$15,MATCH("*Bank of America*",$A$1:$A$15,0),1),1),"")

 

[ELENA911]

I have the below table as a summary:
<TABLE style="WIDTH: 237pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=315><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: #1b3a7e; WIDTH: 83pt; HEIGHT: 31.5pt; BORDER-TOP: #1b3a7e 1pt solid; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr id=td_post_2857439 class=xl87 height=42 rowSpan=2 width=110>Group</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: #1b3a7e; WIDTH: 42pt; BORDER-TOP: #1b3a7e 1pt solid; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl87 rowSpan=2 width=56>Long Term</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: #1b3a7e; WIDTH: 36pt; BORDER-TOP: #1b3a7e 1pt solid; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl87 rowSpan=2 width=48>Short Term</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: #1b3a7e; WIDTH: 43pt; BORDER-TOP: #1b3a7e 1pt solid; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl87 rowSpan=2 width=57> Value</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: #1b3a7e; WIDTH: 33pt; BORDER-TOP: #1b3a7e 1pt solid; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl87 rowSpan=2 width=44>Total</TD></TR><TR style="HEIGHT: 16.5pt" height=22></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 18pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl85 height=24 width=110>Germany</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=56> 4</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 36pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=48> 8</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=57> 10 </TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=44>22</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 18pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl85 height=24 width=110>France</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=56> 10</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 36pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=48> 5</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=57> - </TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=44>15</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 18pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl85 height=24 width=110>United Kingdom</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=56>5</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 36pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=48>6</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=57> 14 </TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=44>25</TD></TR></TBODY></TABLE>

in a different table i have data in columns A:C

Column A Column B
(Group) (Value)

<TABLE style="WIDTH: 254pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=339><COLGROUP><COL style="WIDTH: 174pt; mso-width-source: userset; mso-width-alt: 8484" width=232><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 174pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=16 width=232 align=left>France, Inc </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 width=107 align=left>0</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=16 align=left>Germany, Com </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 align=left>10</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=16 align=left>French </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 align=left>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=20 align=left></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 align=left></TD></TR></TBODY></TABLE>

I want the first table to match under France/Value = 0
But the Group name doesnt match in table one to table two.

 

[T. Valko]

Hi,

I am trying to combine the following functions in excel:

Vlookup
Find
Left

I am workign on a spreadsheet and there are few tables in there that I need to obtain information on the summary page.
My issue is vlookup only works if the value is exact or close enough. I need to put a condition if the criteria doesnt meet to find the lookup value within the cell and return the value in the cell adjacent to it. Can someoen please help me?


for Example in my summary table i have the following name:


Bank of America

i am looking it up in a different table where I have

Apple 10
Bank of New York 5
Bank of America, Inc 5

I need a function that will find bank of america and input 5 in the summary table.

is it possible?

thanks

Another possibility...

As long as there is only one instance of your lookup value...

=SUMIF(A2:A10,"Bank of America*",B2:B10)

Or, using a cell to hold the lookup value:

D2 = Bank of America

=SUMIF(A2:A10,D2&"*",B2:B10)

 

[ELENA911]

I dont think I am explaining my problem clearly. Sorry about that.

I don't need it to sum I just need it to input the value in Column B its not necesserily populated with numbers it can have text in there as well.

I updated the Second table please see below.


<TABLE style="WIDTH: 254pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=339><COLGROUP><COL style="WIDTH: 174pt; mso-width-source: userset; mso-width-alt: 8484" width=232><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 174pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86 height=16 width=232 align=left>Column A (Group)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 80pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86 width=107 align=left>Column B(ID)</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=16 align=left>France, INC</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 align=left>0B1AH</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=16 align=left>Germany</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 align=left>7FC7</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=16 align=left>French </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 align=left>002</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=20 align=left>France, INC</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 align=left>0B1AH</TD></TR></TBODY></TABLE>

 

[T. Valko]

I dont think I am explaining my problem clearly. Sorry about that.

I don't need it to sum I just need it to input the value in Column B its not necesserily populated with numbers it can have text in there as well.

I updated the Second table please see below.


<TABLE style="WIDTH: 254pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=339 border=0><COLGROUP><COL style="WIDTH: 174pt; mso-width-source: userset; mso-width-alt: 8484" width=232><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 12pt" height=16><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 174pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: #d8d8d8" align=left width=232 height=16>Column A (Group)</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 80pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8" align=left width=107>Column B(ID)</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" align=left height=16>France, INC</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>0B1AH</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" align=left height=16>Germany</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>7FC7</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" align=left height=16>French </TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>002</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=left height=20>France, INC</TD><TD class=xl85 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left>0B1AH</TD></TR></TBODY></TABLE>

Maybe this...

Book1
	A	B	C	D	E
2	France, INC	0B1AH	_	Lookup	France
3	Germany	7FC7	_	Result	0B1AH
4	French	002	_	_	_
5	France, INC	0B1AH	_	_	_
Sheet1

This formula entered in E3:

=VLOOKUP(E2&"*",A2:B5,2,0)

 

[ELENA911]

That works. Awesome thank you so much

 

[Aladin Akyurek]

I have the below table as a summary:
<TABLE style="WIDTH: 237pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=315><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: #1b3a7e; WIDTH: 83pt; HEIGHT: 31.5pt; BORDER-TOP: #1b3a7e 1pt solid; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr id=td_post_2857439 class=xl87 height=42 rowSpan=2 width=110>Group</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: #1b3a7e; WIDTH: 42pt; BORDER-TOP: #1b3a7e 1pt solid; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl87 rowSpan=2 width=56>Long Term</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: #1b3a7e; WIDTH: 36pt; BORDER-TOP: #1b3a7e 1pt solid; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl87 rowSpan=2 width=48>Short Term</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: #1b3a7e; WIDTH: 43pt; BORDER-TOP: #1b3a7e 1pt solid; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl87 rowSpan=2 width=57>Value</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: #1b3a7e; WIDTH: 33pt; BORDER-TOP: #1b3a7e 1pt solid; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl87 rowSpan=2 width=44>Total</TD></TR><TR style="HEIGHT: 16.5pt" height=22></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 18pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl85 height=24 width=110>Germany</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=56>4</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 36pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=48>8</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=57>10 </TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=44>22</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 18pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl85 height=24 width=110>France</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=56>10</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 36pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=48>5</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=57>- </TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=44>15</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 18pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl85 height=24 width=110>United Kingdom</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=56>5</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 36pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=48>6</TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=57>14 </TD><TD style="BORDER-BOTTOM: #1b3a7e 1pt solid; BORDER-LEFT: #1b3a7e; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: #1b3a7e; BORDER-RIGHT: #1b3a7e 1pt solid" dir=ltr class=xl86 width=44>25</TD></TR></TBODY></TABLE>

in a different table i have data in columns A:C

Column A Column B
(Group) (Value)

<TABLE style="WIDTH: 254pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=339><COLGROUP><COL style="WIDTH: 174pt; mso-width-source: userset; mso-width-alt: 8484" width=232><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 174pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=16 width=232 align=left>France, Inc </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 width=107 align=left>0</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=16 align=left>Germany, Com </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 align=left>10</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=16 align=left>French </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 align=left>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 height=20 align=left></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl85 align=left></TD></TR></TBODY></TABLE>

I want the first table to match under France/Value = 0
But the Group name doesnt match in table one to table two.

Looks like you want...

<TABLE style="WIDTH: 458pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=611><COLGROUP><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4039" width=114><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2616" span=2 width=74><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1621" width=46><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1308" width=37><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1166" width=33><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 938" width=26><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1166" width=33><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3925" width=110><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt; mso-height-source: userset" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #1b3a7e; WIDTH: 85pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl65 height=19 width=114>Group</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #1b3a7e; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl65 width=74>Long Term</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #1b3a7e; WIDTH: 55pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl65 width=74>Short Term</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #1b3a7e; WIDTH: 34pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl65 width=46>Value</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #1b3a7e; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl65 width=37>Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=33></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 20pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=26></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=33></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=110>Group</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=64>Value</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 85pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 height=19 width=114>Germany</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=74 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=74 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 34pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=46 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 28pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=37 align=right>22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 83pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=110>France, Inc </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>- </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 85pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 height=19 width=114>France</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=74 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=74 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 34pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=46>- </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 28pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=37 align=right>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 83pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=110>Germany, Com </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>10</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 85pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 height=19 width=114>United Kingdom</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=74 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=74 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 34pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=46 align=right>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 28pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" dir=ltr class=xl67 width=37 align=right>25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 83pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=110>French </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=center>#N/A</TD></TR></TBODY></TABLE>

A1:E4 houses the sample table, I2:I4 sample look up values.

J2, just enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$4,$I2),
    INDEX($B$2:$E$4,0,MATCH(J$1,$B$1:$E$1,0)))

If it's other way around, try something like:

=INDEX($J$2:$J$4,MATCH("*"&A2&"*",$I$2:$I$4,0))

 

[T. Valko]

That works. Awesome thank you so much

You're welcome. Thanks for the feedback!