lookop or index?

[Guest]

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 Kabel]

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,'balance_sheet'!$A$1:$A$
100&'balance_sheet'!$B$1:$B$100,0))

 

[Dave R.]

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]

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))

 

[Dave R.]

Darn, should have seen that, thanks!

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))