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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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