Nested If + If Error + Vlookup formula help needed

aravindhan_31

Well-known Member
Joined
Apr 11, 2006
Messages
672
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. 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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
Unable to attach

lookup formula and result required
1638969738771.png


Table array
1638969760396.png
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top