Vlookup for text

[C_tro]

Hi,

I'm trying to use a vlookup function to return a cell with a text an not a value.
I would like to know for each cost center what is the transaction currency used in a table like the one below. Anyone would be able to help?
<TABLE style="WIDTH: 296pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=392><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" span=3 width=98><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 74pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=98>Cost Center</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=98>Company Code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=98>Cost element group name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=98>Transaction Currency</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 30pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=40 width=98>20107201</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl64 width=98>US10</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl64 width=98>FC-Depreciation</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl64 width=98>USD</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; HEIGHT: 30pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl65 height=40 width=98>20107201</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl64 width=98>US10</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl64 width=98>FC-Facility/Prop</TD><TD style="BORDER-BOTTOM: #d0d7e5 0.5pt solid; BORDER-LEFT: #d0d7e5; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d0d7e5; BORDER-RIGHT: #d0d7e5 0.5pt solid" class=xl64 width=98>USD</TD></TR></TBODY></TABLE>
Thanks a lot!

 

[Andrew Poulsom]

Welcome to MrExcel.

What have you tried? The VLOOKUP function can look up and return text.

 

[CharlesChuckieCharles]

Assuming the costcenter to lookup is in (impossible) cell ZZZ1

For Vlookup the keys to check have to be in the left hand column of the data array you wish to query

=Vlookup(ZZZ1,Sheet1!$A$1:$D$2,4,FALSE)

4 is the offset column count (starting with the key column as 1) to return Currency code USD

FALSE return #N/A if the key in ZZZ1 is not found in column A of sheet1 (the data array)

 

[C_tro]

Hi,

thanks for the welcome and the fast answers.
I tried the following formula:
vlookup("cost center X",table!a1:d100,4,false) and instead of the expected USD or EUR I got N/A.

if vlookup allows to return a text then I do not understand

The exact formula is: =VLOOKUP(B3,[LC_FC.xlsx]dexasview8LC!$B$2:$E$600,4,FALSE)
but I used in another part the following formula which works:=vlooKUP($B$3&$A16,[LC_FC.xlsx]dexasview8LC!$A$1:$Q$500,6+Y$1,FALSE), the data in cell B3 is accepted.

any idea on what is wrong?

 

[Andrew Poulsom]

VLOOKUP will return #N/A if it can't find the lookup value in the first column of the table.

 

[C_tro]

Don't ask me why but now it works!
Thanks anyway for your help!