Formula- Lookup multiple values.

[Ebraham]

Hi All,

i would like to vlookup using Base values and first 4 numbers of amount columns from the data sheet. Desired result is what is required. Kindly advise.

Data Sheet
[TABLE="width: 285"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Amount[/TD]
[TD]Base[/TD]
[TD]GFRN[/TD]
[/TR]
[TR]
[TD]-13568973.89[/TD]
[TD]145045[/TD]
[TD]HU641814-1[/TD]
[/TR]
[TR]
[TD]1247[/TD]
[TD]145045[/TD]
[TD]HU641816-1[/TD]
[/TR]
[TR]
[TD]7215816[/TD]
[TD]4130[/TD]
[TD]HU641808-1[/TD]
[/TR]
[TR]
[TD]14190476[/TD]
[TD]4130[/TD]
[TD]HU1278252-1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]4130[/TD]
[TD]HU990182-3[/TD]
[/TR]
[TR]
[TD]-2370526545[/TD]
[TD]4130[/TD]
[TD]HU1361846-2[/TD]
[/TR]
[TR]
[TD]880.45746[/TD]
[TD]4130[/TD]
[TD]HU642007-1[/TD]
[/TR]
[TR]
[TD]-7215825.533[/TD]
[TD]4130[/TD]
[TD]HU642007-1[/TD]
[/TR]
[TR]
[TD]343017.25[/TD]
[TD]4130[/TD]
[TD]HU2174384-1[/TD]
[/TR]
[TR]
[TD]-9242795.143[/TD]
[TD]4130[/TD]
[TD]HU1361846-2[/TD]
[/TR]
</tbody>[/TABLE]

[U]Answer
[/U][TABLE="width: 285"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Base[/TD]
[TD]Amount HUF[/TD]
[TD]Desired result[/TD]
[/TR]
[TR]
[TD]145045[/TD]
[TD]-13568973.89  [/TD]
[TD]HU641814-1[/TD]
[/TR]
[TR]
[TD]145045[/TD]
[TD]124756[/TD]
[TD]HU641816-1[/TD]
[/TR]
[TR]
[TD]4130[/TD]
[TD]-23705755.42[/TD]
[TD]HU1361846-2[/TD]
[/TR]
[TR]
[TD]4130[/TD]
[TD]-9242795.143[/TD]
[TD]HU1361846-2[/TD]
[/TR]
[TR]
[TD]4130[/TD]
[TD]-7215825.533[/TD]
[TD]HU642007-1[/TD]
[/TR]
[TR]
[TD]4130[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4130[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4130[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4130[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4130[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

Try this

=INDEX('Data Sheet'!$C$2:$C$11,MATCH(A2&LEFT(B2,4),'Data Sheet'!$B$2:$B$11&LEFT('Data Sheet'!$A$2:$A$11,4),0))

This is an array formula so use Ctrl+Shift+Enter

 

[Ebraham]

Try this

=INDEX('Data Sheet'!$C$2:$C$11,MATCH(A2&LEFT(B2,4),'Data Sheet'!$B$2:$B$11&LEFT('Data Sheet'!$A$2:$A$11,4),0))

This is an array formula so use Ctrl+Shift+Enter

thanks. Worked well