If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
lookop or index?
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 |
#3
|
|||
|
|||
lookop or index?
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 |
#4
|
|||
|
|||
lookop or index?
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 |
#5
|
|||
|
|||
lookop or index?
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 |
Thread Tools | |
Display Modes | |
|
|