aravindhan_31
Well-known Member
- Joined
- Apr 11, 2006
- Messages
- 672
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
Hi,
sorry if my subject line was bit confusing, Need some help in one of the formula.. I have tried with multiple option, but getting stuck.
I want to do a simple vlookups, where check for 1 value in 1 range and get result , if not found, then look for 2nd value 2nd range and get result, and so on until i get result. In simple first do lookup in Table 1, if not found then Table 2, if not found Table 3 etc..
=IFERROR(VLOOKUP(A4,'Cash non Cash'!$DE:$DF,2,0),IFERROR(VLOOKUP(B4,'Cash non Cash'!$DG:$DH,2,0),IFERROR(VLOOKUP(C4,'Cash non Cash'!$DI:$DJ,2,0),IFERROR(VLOOKUP(D4,'Cash non Cash'!$DK:$DL,2,0),IFERROR(VLOOKUP(E4,'Cash non Cash'!$DM:$DN,2,0),IFERROR(VLOOKUP(F4,'Cash non Cash'!$DO:$DP,2,0),IFERROR(VLOOKUP(G4,'Cash non Cash'!$DQ:$DR,2,0),IFERROR(VLOOKUP(H4,'Cash non Cash'!$DS:$DT,2,0),"nEXT"))))))))
Where I am stuck is. I want to add an if condition that tells me in which table( table array) there result is found.
If my first vlookup gets a value based on VLOOKUP(A4,'Cash non Cash'!$DE:$DF,2,0) then give a result as "Value found in Table 1" the result of this vlookup is #N/A then do second lookup
VLOOKUP(B4,'Cash non Cash'!$DG:$DH,2,0) and if result is found then give result as "Value found in Table 2" and so on till 8 tables.
I tried this, but not working
=IF(VLOOKUP(A4,'Cash non Cash'!DE:DF,2,0)>0,"Table 1",IF(VLOOKUP(B4,'Cash non Cash'!DG:DH,2,0)>0,"Table 2",IF(VLOOKUP(C4,'Cash non Cash'!DI:DJ,2,0)>0,"Table 3",IF(VLOOKUP(D4,'Cash non Cash'!DK:DL,2,0)>0,"Table 4",IF(VLOOKUP(E4,'Cash non Cash'!DM:DN,2,0)>0,"Table 5",IF(VLOOKUP(F4,'Cash non Cash'!DO:DP,2,0)>0,"Table 6",IF(VLOOKUP(G4,'Cash non Cash'!DQ:DR,2,0)>0,"Table 7",IF(VLOOKUP(H4,'Cash non Cash'!DS:DT,2,0)>0,"Table 8","Next"))))))))
Thanks for the help
Regards
Arvind
sorry if my subject line was bit confusing, Need some help in one of the formula.. I have tried with multiple option, but getting stuck.
I want to do a simple vlookups, where check for 1 value in 1 range and get result , if not found, then look for 2nd value 2nd range and get result, and so on until i get result. In simple first do lookup in Table 1, if not found then Table 2, if not found Table 3 etc..
=IFERROR(VLOOKUP(A4,'Cash non Cash'!$DE:$DF,2,0),IFERROR(VLOOKUP(B4,'Cash non Cash'!$DG:$DH,2,0),IFERROR(VLOOKUP(C4,'Cash non Cash'!$DI:$DJ,2,0),IFERROR(VLOOKUP(D4,'Cash non Cash'!$DK:$DL,2,0),IFERROR(VLOOKUP(E4,'Cash non Cash'!$DM:$DN,2,0),IFERROR(VLOOKUP(F4,'Cash non Cash'!$DO:$DP,2,0),IFERROR(VLOOKUP(G4,'Cash non Cash'!$DQ:$DR,2,0),IFERROR(VLOOKUP(H4,'Cash non Cash'!$DS:$DT,2,0),"nEXT"))))))))
Where I am stuck is. I want to add an if condition that tells me in which table( table array) there result is found.
If my first vlookup gets a value based on VLOOKUP(A4,'Cash non Cash'!$DE:$DF,2,0) then give a result as "Value found in Table 1" the result of this vlookup is #N/A then do second lookup
VLOOKUP(B4,'Cash non Cash'!$DG:$DH,2,0) and if result is found then give result as "Value found in Table 2" and so on till 8 tables.
I tried this, but not working
=IF(VLOOKUP(A4,'Cash non Cash'!DE:DF,2,0)>0,"Table 1",IF(VLOOKUP(B4,'Cash non Cash'!DG:DH,2,0)>0,"Table 2",IF(VLOOKUP(C4,'Cash non Cash'!DI:DJ,2,0)>0,"Table 3",IF(VLOOKUP(D4,'Cash non Cash'!DK:DL,2,0)>0,"Table 4",IF(VLOOKUP(E4,'Cash non Cash'!DM:DN,2,0)>0,"Table 5",IF(VLOOKUP(F4,'Cash non Cash'!DO:DP,2,0)>0,"Table 6",IF(VLOOKUP(G4,'Cash non Cash'!DQ:DR,2,0)>0,"Table 7",IF(VLOOKUP(H4,'Cash non Cash'!DS:DT,2,0)>0,"Table 8","Next"))))))))
Thanks for the help
Regards
Arvind