| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can find a value using two different matching criteria?
Dear Friends,
Col c Col d Formula Lease Term Company # 60 2 3% 36 3 7% 48 4 3% 60 5 2% 12 6 20% 24 7 FALSE 36 8 FALSE 48 10 5% 60 9 FALSE Residual Table Company # 1 2 7 4 3 8 5 6 9 10 Col b Lease Term Residual % 12 10% 20% 9% 24 5% 9% 6% 36 4% 7% 3% 48 3% 5% 2% 60 2% 3% 1% =IF(D9=$C$23,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$ 24,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$25,VLOOKUP (C9,$B$30:$E$34,2,0),IF(D9=$D$23,VLOOKUP(C9,$B$30: $E$34,3,0),IF(D9=$D$24,VLOOKUP(C9,$B$30:$E$34,3,0) ,IF(D9=$D$25,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$ 26,VLOOKUP(C9,$B$30:$E$34,3,0)))))))) How can I get a residual % by comaring first Company # and then by months from Residual % Table? the above formula worked except for Company # 7.8.9. The reason, it allows only 7 time to use IF or Vlookup function which is already used by col C and D. Is there any other way to create a formula. the result has to be one column. Thanks a lot for help. dinesh |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can find a value using two different matching criteria?
From your formula
=IF(D9=$C$23,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$ 24,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$25,VLOOKUP (C9,$B$30:$E$34,2,0),IF(D9=$D$23,VLOOKUP(C9,$B$30: $E$34,3,0),IF(D9=$D$24,VLOOKUP(C9,$B$30:$E$34,3,0) ,IF(D9=$D$25,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$ 26,VLOOKUP(C9,$B$30:$E$34,3,0)))))))) it appears that if D9 is in the range c23:d26 you want to do the vlookup, so the following formula appears to work: =IF(COUNTIF($C$23:$D$26,D9) 0,VLOOKUP(C9,$B$30:$E$34,2,FALSE),"") -- Kevin Vaughn "Dinesh" wrote: Dear Friends, Col c Col d Formula Lease Term Company # 60 2 3% 36 3 7% 48 4 3% 60 5 2% 12 6 20% 24 7 FALSE 36 8 FALSE 48 10 5% 60 9 FALSE Residual Table Company # 1 2 7 4 3 8 5 6 9 10 Col b Lease Term Residual % 12 10% 20% 9% 24 5% 9% 6% 36 4% 7% 3% 48 3% 5% 2% 60 2% 3% 1% =IF(D9=$C$23,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$ 24,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$25,VLOOKUP (C9,$B$30:$E$34,2,0),IF(D9=$D$23,VLOOKUP(C9,$B$30: $E$34,3,0),IF(D9=$D$24,VLOOKUP(C9,$B$30:$E$34,3,0) ,IF(D9=$D$25,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$ 26,VLOOKUP(C9,$B$30:$E$34,3,0)))))))) How can I get a residual % by comaring first Company # and then by months from Residual % Table? the above formula worked except for Company # 7.8.9. The reason, it allows only 7 time to use IF or Vlookup function which is already used by col C and D. Is there any other way to create a formula. the result has to be one column. Thanks a lot for help. dinesh |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can find a value using two different matching criteria?
I think this will give you what you want:
=IF(OR(D9=$C$23,D9=$C$24,D9=$C$25),VLOOKUP(C9,$B$3 0:$E$34,2,0),IF(OR(D9=$D$23,D9=$D$24,D9=$D$25,D9=$ D$26),VLOOKUP(C9,$B$30:$E$34,3,0),VLOOKUP(C9,$B$30 :$E$34,4,0))) If D9 is 1, 4 or 5 the residual% is the first column, if D9 is 2, 3, 6 or 10 the residual% is the middle column, otherwise it is the last column, and in each case the value of C9 determines which row of the table is used. Hope this helps. Pete |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can find a value using two different matching criteria?
Pete,
Thanks a bunch. It works perfectly. "Pete" wrote: I think this will give you what you want: =IF(OR(D9=$C$23,D9=$C$24,D9=$C$25),VLOOKUP(C9,$B$3 0:$E$34,2,0),IF(OR(D9=$D$23,D9=$D$24,D9=$D$25,D9=$ D$26),VLOOKUP(C9,$B$30:$E$34,3,0),VLOOKUP(C9,$B$30 :$E$34,4,0))) If D9 is 1, 4 or 5 the residual% is the first column, if D9 is 2, 3, 6 or 10 the residual% is the middle column, otherwise it is the last column, and in each case the value of C9 determines which row of the table is used. Hope this helps. Pete |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can find a value using two different matching criteria?
Kevin,
thanks. it works. dinesh "Kevin Vaughn" wrote: From your formula =IF(D9=$C$23,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$ 24,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$25,VLOOKUP (C9,$B$30:$E$34,2,0),IF(D9=$D$23,VLOOKUP(C9,$B$30: $E$34,3,0),IF(D9=$D$24,VLOOKUP(C9,$B$30:$E$34,3,0) ,IF(D9=$D$25,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$ 26,VLOOKUP(C9,$B$30:$E$34,3,0)))))))) it appears that if D9 is in the range c23:d26 you want to do the vlookup, so the following formula appears to work: =IF(COUNTIF($C$23:$D$26,D9) 0,VLOOKUP(C9,$B$30:$E$34,2,FALSE),"") -- Kevin Vaughn "Dinesh" wrote: Dear Friends, Col c Col d Formula Lease Term Company # 60 2 3% 36 3 7% 48 4 3% 60 5 2% 12 6 20% 24 7 FALSE 36 8 FALSE 48 10 5% 60 9 FALSE Residual Table Company # 1 2 7 4 3 8 5 6 9 10 Col b Lease Term Residual % 12 10% 20% 9% 24 5% 9% 6% 36 4% 7% 3% 48 3% 5% 2% 60 2% 3% 1% =IF(D9=$C$23,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$ 24,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$25,VLOOKUP (C9,$B$30:$E$34,2,0),IF(D9=$D$23,VLOOKUP(C9,$B$30: $E$34,3,0),IF(D9=$D$24,VLOOKUP(C9,$B$30:$E$34,3,0) ,IF(D9=$D$25,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$ 26,VLOOKUP(C9,$B$30:$E$34,3,0)))))))) How can I get a residual % by comaring first Company # and then by months from Residual % Table? the above formula worked except for Company # 7.8.9. The reason, it allows only 7 time to use IF or Vlookup function which is already used by col C and D. Is there any other way to create a formula. the result has to be one column. Thanks a lot for help. dinesh |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can find a value using two different matching criteria?
You're welcome.
-- Kevin Vaughn "Dinesh" wrote: Kevin, thanks. it works. dinesh "Kevin Vaughn" wrote: From your formula =IF(D9=$C$23,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$ 24,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$25,VLOOKUP (C9,$B$30:$E$34,2,0),IF(D9=$D$23,VLOOKUP(C9,$B$30: $E$34,3,0),IF(D9=$D$24,VLOOKUP(C9,$B$30:$E$34,3,0) ,IF(D9=$D$25,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$ 26,VLOOKUP(C9,$B$30:$E$34,3,0)))))))) it appears that if D9 is in the range c23:d26 you want to do the vlookup, so the following formula appears to work: =IF(COUNTIF($C$23:$D$26,D9) 0,VLOOKUP(C9,$B$30:$E$34,2,FALSE),"") -- Kevin Vaughn "Dinesh" wrote: Dear Friends, Col c Col d Formula Lease Term Company # 60 2 3% 36 3 7% 48 4 3% 60 5 2% 12 6 20% 24 7 FALSE 36 8 FALSE 48 10 5% 60 9 FALSE Residual Table Company # 1 2 7 4 3 8 5 6 9 10 Col b Lease Term Residual % 12 10% 20% 9% 24 5% 9% 6% 36 4% 7% 3% 48 3% 5% 2% 60 2% 3% 1% =IF(D9=$C$23,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$ 24,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$25,VLOOKUP (C9,$B$30:$E$34,2,0),IF(D9=$D$23,VLOOKUP(C9,$B$30: $E$34,3,0),IF(D9=$D$24,VLOOKUP(C9,$B$30:$E$34,3,0) ,IF(D9=$D$25,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$ 26,VLOOKUP(C9,$B$30:$E$34,3,0)))))))) How can I get a residual % by comaring first Company # and then by months from Residual % Table? the above formula worked except for Company # 7.8.9. The reason, it allows only 7 time to use IF or Vlookup function which is already used by col C and D. Is there any other way to create a formula. the result has to be one column. Thanks a lot for help. dinesh |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can find a value using two different matching criteria?
Glad to hear it, thanks for feeding back.
Pete |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I compare data in two worksheets to find matching cells? | Excel Discussion (Misc queries) | |||
| Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions | |||
| Find within Workbook. | Excel Discussion (Misc queries) | |||
| Trying to find a max value with criteria | Excel Worksheet Functions | |||
| boolean find criteria in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
