lookop or index?

I'm looking for a way to look up/return a value based on 2
nubers like below:

I need the balance for Account 57500, & Cost Center 125
from a trial balance table on a separate tab. I've used
vlookup before w/ a named range but that won't work in
this situation.

Thanks & let me know if you need more details,
Dan

Hi Dan
if your account numbers are in column A and your cost centers in column
B and you want to get the value form column C
try the following (assumption A1, B1 contain the lookup values) array
formula (entered with CTRL+sHIFT+eNTER)
=INDEX('balance_sheet'!$C$1:$C$100,MATCH(A1&B1,'ba lance_sheet'!$A$1:$A$
100&'balance_sheet'!$B$1:$B$100,0))

--
Regards
Frank Kabel
Frankfurt, Germany

wrote:
I'm looking for a way to look up/return a value based on 2
nubers like below:

I need the balance for Account 57500, & Cost Center 125
from a trial balance table on a separate tab. I've used
vlookup before w/ a named range but that won't work in
this situation.

Thanks & let me know if you need more details,
Dan

Frank, I came up with a similar formula (array entered)

=INDEX(F$8:F$11,MATCH(G8&H8,D$8$11&E$8:E$11),0)

but it doesn't return the value it should, at least on my machine. I'd think
yours would act similarly, the sheet references shouldn't affect it.

Does it work right for you?


"Frank Kabel" wrote in message
...
Hi Dan
if your account numbers are in column A and your cost centers in column
B and you want to get the value form column C
try the following (assumption A1, B1 contain the lookup values) array
formula (entered with CTRL+sHIFT+eNTER)
=INDEX('balance_sheet'!$C$1:$C$100,MATCH(A1&B1,'ba lance_sheet'!$A$1:$A$
100&'balance_sheet'!$B$1:$B$100,0))

--
Regards
Frank Kabel
Frankfurt, Germany

wrote:
I'm looking for a way to look up/return a value based on 2
nubers like below:

I need the balance for Account 57500, & Cost Center 125
from a trial balance table on a separate tab. I've used
vlookup before w/ a named range but that won't work in
this situation.

Thanks & let me know if you need more details,
Dan

Hi Dave
I think you messed with placing the zero :-)
Try
=INDEX(F$8:F$11,MATCH(G8&H8,D$8$11&E$8:E$11,0))

--
Regards
Frank Kabel
Frankfurt, Germany

Dave R. wrote:
Frank, I came up with a similar formula (array entered)

=INDEX(F$8:F$11,MATCH(G8&H8,D$8$11&E$8:E$11),0)

but it doesn't return the value it should, at least on my machine.
I'd think yours would act similarly, the sheet references shouldn't
affect it.

Does it work right for you?


"Frank Kabel" wrote in message
...
Hi Dan
if your account numbers are in column A and your cost centers in
column B and you want to get the value form column C
try the following (assumption A1, B1 contain the lookup values)
array
formula (entered with CTRL+sHIFT+eNTER)

=INDEX('balance_sheet'!$C$1:$C$100,MATCH(A1&B1,'ba lance_sheet'!$A$1:$A$
100&'balance_sheet'!$B$1:$B$100,0))

--
Regards
Frank Kabel
Frankfurt, Germany

wrote:
I'm looking for a way to look up/return a value based on 2
nubers like below:

I need the balance for Account 57500, & Cost Center 125
from a trial balance table on a separate tab. I've used
vlookup before w/ a named range but that won't work in
this situation.

Thanks & let me know if you need more details,
Dan

Darn, should have seen that, thanks!


"Frank Kabel" wrote in message
...
Hi Dave
I think you messed with placing the zero :-)
Try
=INDEX(F$8:F$11,MATCH(G8&H8,D$8$11&E$8:E$11,0))

--
Regards
Frank Kabel
Frankfurt, Germany

Dave R. wrote:
Frank, I came up with a similar formula (array entered)

=INDEX(F$8:F$11,MATCH(G8&H8,D$8$11&E$8:E$11),0)

but it doesn't return the value it should, at least on my machine.
I'd think yours would act similarly, the sheet references shouldn't
affect it.

Does it work right for you?


"Frank Kabel" wrote in message
...
Hi Dan
if your account numbers are in column A and your cost centers in
column B and you want to get the value form column C
try the following (assumption A1, B1 contain the lookup values)
array
formula (entered with CTRL+sHIFT+eNTER)

=INDEX('balance_sheet'!$C$1:$C$100,MATCH(A1&B1,'ba lance_sheet'!$A$1:$A$
100&'balance_sheet'!$B$1:$B$100,0))

--
Regards
Frank Kabel
Frankfurt, Germany

wrote:
I'm looking for a way to look up/return a value based on 2
nubers like below:

I need the balance for Account 57500, & Cost Center 125
from a trial balance table on a separate tab. I've used
vlookup before w/ a named range but that won't work in
this situation.

Thanks & let me know if you need more details,
Dan